Re: Transactions involving multiple postgres foreign servers, take 2

On Mon, 15 Jun 2020 at 15:20, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sun, Jun 14, 2020 at 2:21 PM Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
> >
> > >> 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.
>
> Okay, I understand that the purpose of this feature is to provide
> atomic commit which means the transaction on all servers involved will
> either commit or rollback. However, I think we should at least see at
> a high level how the visibility will work because it might influence
> the implementation of this feature.
>
> > > 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.

Regards,

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