Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, 12 Jun 2020 at 15:37, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Jun 12, 2020 at 9:54 AM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > 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).
> >
>
> Which are these other online transactions? I had assumed that foreign
> transaction resolver process is to resolve in-doubt transactions but
> it seems it is also used for some other purpose which anyway was the
> next question I had while reviewing other sections of docs but let's
> clarify as it came up now.

When a distributed transaction is committed by COMMIT command, the
postgres backend process prepare all foreign transaction and commit
the local transaction. Then the backend enqueue itself to the shmem
queue, asks a resolver process for committing the prepared foreign
transaction, and wait. That is, these prepared foreign transactions
are committed by the resolver process, not backend process. Once the
resolver process committed all prepared foreign transactions, it wakes
the waiting backend process. I meant this kind of transaction is
on-line transactions. This procedure is similar to what synchronous
replication does.

Regards,

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