[HACKERS] Postgres_fdw behaves oddly

Hello,

I have tested some scenarios of inserting data into two foreign tables using postgres_fdw. All the test cases works fine except Test 5.

In Test 5, I am expecting error as both the rows violates the constraint. But at the COMMIT time transaction does not give any error and it takes lock waiting for a transaction to finish.

Please check the below tests:

postgres=# CREATE SERVER loopback1 FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS (dbname 'postgres');
CREATE SERVER
postgres=# CREATE SERVER loopback2 FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS (dbname 'postgres');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER loopback1;
CREATE USER MAPPING
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING

-- Here local table is created to refer as foreign table. The table has constraints which are deferred till end of transaction.
-- This allows COMMIT time errors to occur by inserting data which violates constraints.

postgres=# CREATE TABLE lt(val int UNIQUE DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE ft1_lt (val int) SERVER loopback1 OPTIONS (table_name 'lt');
CREATE FOREIGN TABLE
postgres=# CREATE FOREIGN TABLE ft2_lt (val int) SERVER loopback2 OPTIONS (table_name 'lt');
CREATE FOREIGN TABLE

Test 1:
=======
In a transaction insert two rows one each to the two foreign tables and it works fine.

postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO ft1_lt VALUES (1);
INSERT 0 1
postgres=# INSERT INTO ft2_lt VALUES (3);
INSERT 0 1
postgres=# COMMIT;
COMMIT
postgres=# SELECT * FROM lt;
 val
-----
   1
   3
(2 rows)

Test 2:
=======
In a transaction insert two rows one each to the two foreign tables.
One of the rows violates the constraint and other not. At the time of COMMIT one of the foreign server violates the constraints so it return error. I think this is also expected behavior.
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO ft1_lt VALUES (1); -- Violates constraint
INSERT 0 1
postgres=# INSERT INTO ft2_lt VALUES (4);
INSERT 0 1
postgres=# COMMIT;
2017-02-03 15:26:28.667 JST [3081] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:26:28.667 JST [3081] DETAIL:  Key (val)=(1) already exists.
2017-02-03 15:26:28.667 JST [3081] STATEMENT:  COMMIT TRANSACTION
2017-02-03 15:26:28.668 JST [3075] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:26:28.668 JST [3075] DETAIL:  Key (val)=(1) already exists.
2017-02-03 15:26:28.668 JST [3075] CONTEXT:  Remote SQL command: COMMIT TRANSACTION
2017-02-03 15:26:28.668 JST [3075] STATEMENT:  COMMIT;
2017-02-03 15:26:28.668 JST [3081] WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ERROR:  duplicate key value violates unique constraint "lt_val_key"
DETAIL:  Key (val)=(1) already exists.
CONTEXT:  Remote SQL command: COMMIT TRANSACTION
postgres=#
postgres=#
postgres=# SELECT * FROM lt;
 val
-----
   1
   3
(2 rows)

Test 3:
=======
In a transaction insert two rows one each to the two foreign tables.
One of the rows violates the constraint and other not. At the time of COMMIT one of the foreign server violates the constraints so it return error. I think this is also expected behavior.
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO ft1_lt VALUES (4);
INSERT 0 1
postgres=# INSERT INTO ft2_lt VALUES (3); -- Violates constraint
INSERT 0 1
postgres=# COMMIT;
2017-02-03 15:27:14.331 JST [3084] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:27:14.331 JST [3084] DETAIL:  Key (val)=(3) already exists.
2017-02-03 15:27:14.331 JST [3084] STATEMENT:  COMMIT TRANSACTION
2017-02-03 15:27:14.332 JST [3075] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:27:14.332 JST [3075] DETAIL:  Key (val)=(3) already exists.
2017-02-03 15:27:14.332 JST [3075] CONTEXT:  Remote SQL command: COMMIT TRANSACTION
2017-02-03 15:27:14.332 JST [3075] STATEMENT:  COMMIT;
2017-02-03 15:27:14.332 JST [3084] WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ERROR:  duplicate key value violates unique constraint "lt_val_key"
DETAIL:  Key (val)=(3) already exists.
CONTEXT:  Remote SQL command: COMMIT TRANSACTION
postgres=# SELECT * FROM lt;
 val
-----
   1
   3
   4
(3 rows)
Test 4:
=======
In a transaction insert two rows one each to the two foreign tables.
Both the rows violates the constraint. So at the time of COMMIT it returns error. I think this is also expected behavior.

postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO ft1_lt VALUES (1); -- Violates constraint
INSERT 0 1
postgres=# INSERT INTO ft2_lt VALUES (3); -- Violates constraint
INSERT 0 1
postgres=# COMMIT;
2017-02-03 15:29:18.857 JST [3081] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:29:18.857 JST [3081] DETAIL:  Key (val)=(1) already exists.
2017-02-03 15:29:18.857 JST [3081] STATEMENT:  COMMIT TRANSACTION
2017-02-03 15:29:18.858 JST [3075] ERROR:  duplicate key value violates unique constraint "lt_val_key"
2017-02-03 15:29:18.858 JST [3075] DETAIL:  Key (val)=(1) already exists.
2017-02-03 15:29:18.858 JST [3075] CONTEXT:  Remote SQL command: COMMIT TRANSACTION
2017-02-03 15:29:18.858 JST [3075] STATEMENT:  COMMIT;
2017-02-03 15:29:18.858 JST [3081] WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ERROR:  duplicate key value violates unique constraint "lt_val_key"
DETAIL:  Key (val)=(1) already exists.
CONTEXT:  Remote SQL command: COMMIT TRANSACTION
postgres=#
postgres=# SELECT * FROM lt;
 val
-----
   1
   3
   4
(3 rows)
Test 5:
=======
In a transaction insert two rows one each to the two foreign tables.
Both the rows violates the constraint. Here error is expected at COMMIT time but transaction does not give any error and it takes lock waiting for a transaction to finish.
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO ft1_lt VALUES (3); -- Violates constraint
INSERT 0 1
postgres=# INSERT INTO ft2_lt VALUES (3); -- Violates constraint
INSERT 0 1
postgres=# COMMIT;
.
.
.

postgres=# select datid,datname,pid,wait_event_type,wait_event,query from pg_stat_activity;
-[ RECORD 1 ]---+---------------------------------------------------------------------------------
datid           | 13123
datname         | postgres
pid             | 3654
wait_event_type | Lock
wait_event      | transactionid
query           | COMMIT TRANSACTION

Note: Test 4 and Test 5 are same but in Test 5 both the foreign servers trying to insert the same data.

Is this a expected behavior of postgres_fdw?

Regards,
Vinayak Pokale

NTT Open Source Software Center