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
