Re: [HACKERS] Postgres_fdw behaves oddly

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