Re: Transactions involving multiple postgres foreign servers, take 2

>> 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.

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.

This approach was proposed in a academic paper [1].

Good point with the approach is, we don't need to modify PostgreSQL at
all.

Downside of the approach is, we need someone who controls the timings
(in [1], a middleware called "Pangea" was proposed). Also we need to
limit the transaction isolation level to REPEATABLE READ.

[1] http://www.vldb.org/pvldb/vol2/vldb09-694.pdf

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp