Re: Transactions involving multiple postgres foreign servers, take 2

On Tue, Jun 16, 2020 at 3:40 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Jun 15, 2020 at 7:06 PM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > On Mon, 15 Jun 2020 at 15:20, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > >
> > > > > Even the local
> > > > > server is not modified, since a resolver process commits prepared
> > > > > foreign transactions one by one another user could see an inconsistent
> > > > > result. Providing globally consistent snapshots to transactions
> > > > > involving foreign servers is one of the solutions.
> > >
> > > How would it be able to do that? Say, when it decides to take a
> > > snapshot the transaction on the foreign server appears to be committed
> > > but the transaction on the local server won't appear to be committed,
> > > so the consistent data visibility problem as mentioned above could
> > > still arise.
> >
> > There are many solutions. For instance, in Postgres-XC/X2 (and maybe
> > XL), there is a GTM node that is responsible for providing global
> > transaction IDs (GXID) and globally consistent snapshots. All
> > transactions need to access GTM when checking the distributed
> > transaction status as well as starting transactions and ending
> > transactions. IIUC if a global transaction accesses a tuple whose GXID
> > is included in its global snapshot it waits for that transaction to be
> > committed or rolled back.
> >
>
> Is there some mapping between GXID and XIDs allocated for each node or
> will each node use the GXID as XID to modify the data? Are we fine
> with parking the work for global snapshots and atomic visibility to a
> separate patch and just proceed with the design proposed by this
> patch?

Distributed transaction involves, atomic commit, atomic visibility
and global consistency. 2PC is the only practical solution for atomic
commit. There are some improvements over 2PC but those are add ons to
the basic 2PC, which is what this patch provides. Atomic visibility
and global consistency however have alternative solutions but all of
those solutions require 2PC to be supported. Each of those are large
pieces of work and trying to get everything in may not work. Once we
have basic 2PC in place, there will be a ground to experiment with
solutions for global consistency and atomic visibility. If we manage
to do it right, we could make it pluggable as well. So, I think we
should concentrate on supporting basic 2PC work now.

> I am asking because I thought there might be some impact on
> the design of this patch based on what we decide for that work.
>

Since 2PC is at the heart of any distributed transaction system, the
impact will be low. Figuring all of that, without having basic 2PC,
will be very hard.

--
Best Wishes,
Ashutosh Bapat