Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, 12 Jun 2020 at 12:40, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Jun 12, 2020 at 7:59 AM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > On Thu, 11 Jun 2020 at 22:21, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> >
> > > I have another question about
> > > this field, why can't it be one of the status ('preparing',
> > > 'prepared', 'committing', 'aborting', 'in-doubt') rather than having a
> > > separate field?
> >
> > Because I'm using in-doubt field also for checking if the foreign
> > transaction entry can also be resolved manually, i.g.
> > pg_resolve_foreign_xact(). For instance, a foreign transaction which
> > status = 'prepared' and in-doubt = 'true' can be resolved either
> > foreign transaction resolver or pg_resolve_foreign_xact(). When a user
> > execute pg_resolve_foreign_xact() against the foreign transaction, it
> > sets status = 'committing' (or 'rollbacking') by checking transaction
> > status in clog. The user might cancel pg_resolve_foreign_xact() during
> > resolution. In this case, the foreign transaction is still status =
> > 'committing' and in-doubt = 'true'. Then if a foreign transaction
> > resolver process processes the foreign transaction, it can commit it
> > without clog looking.
> >
>
> I think this is a corner case and it is better to simplify the state
> recording of foreign transactions then to save a CLOG lookup.
>

The main usage of in-doubt flag is to distinguish between in-doubt
transactions and other transactions that have their waiter (I call
on-line transactions). If one foreign server downs for a long time
after the server crash during distributed transaction commit, foreign
transaction resolver tries to resolve the foreign transaction but
fails because the foreign server doesn’t respond. We’d like to avoid
the situation where a resolver process always picks up that foreign
transaction and other on-online transactions waiting to be resolved
cannot move forward. Therefore, a resolver process prioritizes online
transactions. Once the shmem queue having on-line transactions becomes
empty, a resolver process looks at the array of foreign transaction
state to get in-doubt transactions to resolve. I think we should not
process both in-doubt transactions and on-line transactions in the
same way.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services