Re: Transactions involving multiple postgres foreign servers, take 2

On Sat, 13 Jun 2020 at 14:02, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Jun 12, 2020 at 6:24 PM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > On Fri, 12 Jun 2020 at 19:24, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > > > 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.
> > > >
> >
> > Thank you for your review comments! Let me answer your question first.
> > I'll see the review comments.
> >
> > >
> > > Does this mean that we will mark the xid as committed in CLOG of the
> > > local server?
> >
> > Well what I meant is that when the client executes COMMIT command, the
> > backend executes PREPARE TRANSACTION command on all involved foreign
> > servers and then marks the xid as committed in clog in the local
> > server.
> >
>
> Won't it create an inconsistency in viewing the data from the
> different servers? Say, such a transaction inserts one row into a
> local server and another into the foreign server. Now, if we follow
> the above protocol, the user will be able to see the row from the
> local server but not from the foreign server.

Yes, you're right. This atomic commit feature doesn't guarantee such
consistent visibility so-called atomic visibility. 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.

Regards,

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