Re: Transactions involving multiple postgres foreign servers, take 2

>> My concern is, FDW+2PC without atomic visibility could lead to data
>> inconsistency among servers in some cases. If my understanding is
>> correct, FDW+2PC (without atomic visibility) cannot prevent data
>> inconsistency in the case below. Initially table t1 has only one row
>> with i = 0 on both N1 and N2. By executing S1 and S2 concurrently, t1
>> now has different value of i, 0 and 1.
>
> IIUC the following sequence won't happen because COMMIT PREPARED
> 's1n1' cannot be executed before PREPARE TRANSACTION 's1n2'.

You are right.

> But as
> you mentioned, we cannot prevent data inconsistency even with FDW+2PC
> e.g., when S2 starts a transaction between COMMIT PREPARED on N1 and
> COMMIT PREPARED on N2 by S1.

Ok, example updated.

S1/N1: DROP TABLE t1;
DROP TABLE
S1/N1: CREATE TABLE t1(i int);
CREATE TABLE
S1/N1: INSERT INTO t1 VALUES(0);
INSERT 0 1
S1/N2: DROP TABLE t1;
DROP TABLE
S1/N2: CREATE TABLE t1(i int);
CREATE TABLE
S1/N2: INSERT INTO t1 VALUES(0);
INSERT 0 1
S1/N1: BEGIN;
BEGIN
S1/N2: BEGIN;
BEGIN
S1/N1: UPDATE t1 SET i = i + 1; -- i = 1
UPDATE 1
S1/N2: UPDATE t1 SET i = i + 1; -- i = 1
UPDATE 1
S2/N1: BEGIN;
BEGIN
S2/N2: BEGIN;
BEGIN
S1/N1: PREPARE TRANSACTION 's1n1';
PREPARE TRANSACTION
S1/N2: PREPARE TRANSACTION 's1n2';
PREPARE TRANSACTION
S2/N1: PREPARE TRANSACTION 's2n1';
PREPARE TRANSACTION
S2/N2: PREPARE TRANSACTION 's2n2';
PREPARE TRANSACTION
S1/N1: COMMIT PREPARED 's1n1';
COMMIT PREPARED
S2/N1: DELETE FROM t1 WHERE i = 1;
DELETE 1
S2/N2: DELETE FROM t1 WHERE i = 1;
DELETE 0
S1/N2: COMMIT PREPARED 's1n2';
COMMIT PREPARED
S2/N1: COMMIT PREPARED 's2n1';
COMMIT PREPARED
S2/N2: COMMIT PREPARED 's2n2';
COMMIT PREPARED
S2/N1: SELECT * FROM t1;
i
---
(0 rows)

S2/N2: SELECT * FROM t1;
i
---
1
(1 row)

> The point is this data inconsistency is
> lead by an inconsistent read but not by an inconsistent commit
> results. I think there are kinds of possibilities causing data
> inconsistency but atomic commit and atomic visibility eliminate
> different possibilities. We can eliminate all possibilities of data
> inconsistency only after we support 2PC and globally MVCC.

IMO any permanent data inconsistency is a serious problem for users no
matter what the technical reasons are.

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