Re: Transactions involving multiple postgres foreign servers, take 2

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.

>
> Another approach to the atomic visibility problem is to control
> snapshot acquisition timing and commit timing (plus using REPEATABLE
> READ). In the REPEATABLE READ transaction isolation level, PostgreSQL
> assigns a snapshot at the time when the first command is executed in a
> transaction. If we could prevent any commit while any transaction is
> acquiring snapshot, and we could prevent any snapshot acquisition while
> committing, visibility inconsistency which Amit explained can be
> avoided.
>

I think the problem mentioned above can occur with this as well or if
I am missing something then can you explain in further detail how it
won't create problem in the scenario I have used above?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com