Обсуждение: [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
Hi, On Fri, 3 Feb 2017 18:12:01 +0900 vinayak <Pokale_Vinayak_q3@lab.ntt.co.jp> wrote: > 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. I can reproduce this with REL9_6_STABLE. The local process (application_name = psql) is waiting "COMMIT TRANSACTION" for returning at pgfdw_xact_callback() (in postgres_fdw/connection.c), and the remote process (application_name = postgres_fdw) is stuck at _bt_doinsert() with XactLockTableWait. I attached the backtrace results. I can't figure out yet why _bt_check_unique() returns without calling ereport(). Regards, > > 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 > -- Yugo Nagata <nagata@sraoss.co.jp> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers