Re: Postgres_fdw behaves oddly

Hi,

On Fri, 3 Feb 2017 18:12:01 +0900
vinayak <Pokale_Vinayak_q3(at)lab(dot)ntt(dot)co(dot)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(at)sraoss(dot)co(dot)jp>