Hi,
On Fri, 3 Feb 2017 18:12:01 +0900
vinayak <[email protected]> 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 <[email protected]>
test=# select * from pg_stat_activity ;
-[ RECORD 1 ]----+---------------------------------
datid | 16384
datname | test
pid | 16626
usesysid | 10
usename | yugo-n
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2017-02-07 20:38:28.629562+09
xact_start | 2017-02-07 20:41:21.978381+09
query_start | 2017-02-07 20:41:29.118791+09
state_change | 2017-02-07 20:41:29.118796+09
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin |
query | end;
-[ RECORD 2 ]----+---------------------------------
datid | 16384
datname | test
pid | 16796
usesysid | 10
usename | yugo-n
application_name | postgres_fdw
client_addr |
client_hostname |
client_port | -1
backend_start | 2017-02-07 20:38:49.664541+09
xact_start | 2017-02-07 20:41:25.25321+09
query_start | 2017-02-07 20:41:25.254316+09
state_change | 2017-02-07 20:41:25.254369+09
wait_event_type |
wait_event |
state | idle in transaction
backend_xid | 9088
backend_xmin | 9088
query | DEALLOCATE pgsql_fdw_prep_4
-[ RECORD 3 ]----+---------------------------------
datid | 16384
datname | test
pid | 16840
usesysid | 10
usename | yugo-n
application_name | postgres_fdw
client_addr |
client_hostname |
client_port | -1
backend_start | 2017-02-07 20:38:54.579606+09
xact_start | 2017-02-07 20:41:27.815021+09
query_start | 2017-02-07 20:41:29.118962+09
state_change | 2017-02-07 20:41:29.118966+09
wait_event_type | Lock
wait_event | transactionid
state | active
backend_xid | 9089
backend_xmin | 9088
query | COMMIT TRANSACTION
$ sudo gdb -p 16626
...
(gdb) bt
#0 0x00007f4116434fc0 in __poll_nocancel () at
../sysdeps/unix/syscall-template.S:81
#1 0x00007f410c3b82a6 in poll (__timeout=<optimized out>, __nfds=1,
__fds=0x7ffcb6936100) at /usr/include/x86_64-linux-gnu/bits/poll2.h:46
#2 pqSocketPoll (end_time=-1, forWrite=0, forRead=1, sock=<optimized out>) at
fe-misc.c:1134
#3 pqSocketCheck (conn=conn@entry=0x2481850, forRead=forRead@entry=1,
forWrite=forWrite@entry=0, end_time=end_time@entry=-1)
at fe-misc.c:1076
#4 0x00007f410c3b8b90 in pqWaitTimed (forRead=forRead@entry=1,
forWrite=forWrite@entry=0, conn=conn@entry=0x2481850,
finish_time=finish_time@entry=-1) at fe-misc.c:1008
#5 0x00007f410c3b8bdc in pqWait (forRead=forRead@entry=1,
forWrite=forWrite@entry=0, conn=conn@entry=0x2481850) at fe-misc.c:991
#6 0x00007f410c3b6e39 in PQgetResult (conn=conn@entry=0x2481850) at
fe-exec.c:1732
#7 0x00007f410c3b712e in PQexecFinish (conn=conn@entry=0x2481850) at
fe-exec.c:2018
#8 0x00007f410c3b71f2 in PQexec (conn=conn@entry=0x2481850,
query=query@entry=0x7f410c5e1041 "COMMIT TRANSACTION") at fe-exec.c:1852
#9 0x00007f410c5ded9f in do_sql_command (conn=0x2481850,
sql=sql@entry=0x7f410c5e1041 "COMMIT TRANSACTION") at connection.c:355
#10 0x00007f410c5df900 in pgfdw_xact_callback (event=XACT_EVENT_PRE_COMMIT,
arg=<optimized out>) at connection.c:624
#11 0x00000000004d33bd in CallXactCallbacks (event=XACT_EVENT_PRE_COMMIT) at
xact.c:3336
#12 CommitTransaction () at xact.c:1980
#13 0x00000000004d42a5 in CommitTransactionCommand () at xact.c:2881
#14 0x00000000006b9925 in finish_xact_command () at postgres.c:2457
#15 0x00000000006bd262 in finish_xact_command () at postgres.c:2448
#16 exec_simple_query (query_string=0x2440ea0 "end;") at postgres.c:1111
#17 PostgresMain (argc=<optimized out>, argv=argv@entry=0x23ed370,
dbname=0x23ed258 "test", username=<optimized out>) at postgres.c:4076
#18 0x000000000046b5fe in BackendRun (port=0x23eaf00) at postmaster.c:4271
#19 BackendStartup (port=0x23eaf00) at postmaster.c:3945
#20 ServerLoop () at postmaster.c:1701
#21 0x0000000000663246 in PostmasterMain (argc=argc@entry=1,
argv=argv@entry=0x23c3140) at postmaster.c:1309
#22 0x000000000046c0fe in main (argc=1, argv=0x23c3140) at main.c:228
$ sudo gdb -p 16840
...
(gdb) bt
#0 0x00007f41164429f3 in __epoll_wait_nocancel () at
../sysdeps/unix/syscall-template.S:81
#1 0x000000000069d926 in WaitEventSetWaitBlock (nevents=1,
occurred_events=0x7ffcb6935830, cur_timeout=-1, set=0x2494d40) at latch.c:990
#2 WaitEventSetWait (set=set@entry=0x2494d40, timeout=timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffcb6935830,
nevents=nevents@entry=1) at latch.c:944
#3 0x000000000069dd46 in WaitLatchOrSocket (latch=0x7f411532465c,
wakeEvents=wakeEvents@entry=1, sock=sock@entry=-1, timeout=-1,
timeout@entry=0) at latch.c:347
#4 0x000000000069dddd in WaitLatch (latch=<optimized out>,
wakeEvents=wakeEvents@entry=1, timeout=timeout@entry=0) at latch.c:302
#5 0x00000000006ac33b in ProcSleep (locallock=locallock@entry=0x23e9030,
lockMethodTable=lockMethodTable@entry=0x8f9960 <default_lockmethod>) at
proc.c:1221
#6 0x00000000006a7438 in WaitOnLock (locallock=locallock@entry=0x23e9030,
owner=owner@entry=0x23eeef8) at lock.c:1703
#7 0x00000000006a8a05 in LockAcquireExtended
(locktag=locktag@entry=0x7ffcb6935b60, lockmode=lockmode@entry=5,
sessionLock=sessionLock@entry=0 '\000', dontWait=dontWait@entry=0 '\000',
reportMemoryError=reportMemoryError@entry=1 '\001')
at lock.c:998
#8 0x00000000006a8d71 in LockAcquire (locktag=locktag@entry=0x7ffcb6935b60,
lockmode=lockmode@entry=5,
sessionLock=sessionLock@entry=0 '\000', dontWait=dontWait@entry=0 '\000')
at lock.c:688
#9 0x00000000006a6aae in XactLockTableWait (xid=xid@entry=9088,
rel=rel@entry=0x7f4116fdff18, ctid=ctid@entry=0x2494c70,
oper=oper@entry=XLTW_InsertIndex) at lmgr.c:587
#10 0x00000000004ad585 in _bt_doinsert (rel=rel@entry=0x7f4116fdff18,
itup=itup@entry=0x2494c70,
checkUnique=checkUnique@entry=UNIQUE_CHECK_EXISTING,
heapRel=heapRel@entry=0x7f4116fdc010) at nbtinsert.c:183
#11 0x00000000004afc81 in btinsert (rel=0x7f4116fdff18, values=<optimized out>,
isnull=<optimized out>, ht_ctid=0x7ffcb69363c4,
heapRel=0x7f4116fdc010, checkUnique=UNIQUE_CHECK_EXISTING) at nbtree.c:284
#12 0x000000000055ab2b in unique_key_recheck (fcinfo=<optimized out>) at
constraint.c:167
#13 0x000000000059a337 in ExecCallTriggerFunc
(trigdata=trigdata@entry=0x7ffcb6936400, tgindx=tgindx@entry=0,
finfo=finfo@entry=0x2490db0,
instr=instr@entry=0x0, per_tuple_context=per_tuple_context@entry=0x23f8048)
at trigger.c:1909
#14 0x000000000059b8a3 in AfterTriggerExecute (trigdesc=0x2490ca0,
trigdesc=0x2490ca0, trig_tuple_slot2=0x0, trig_tuple_slot1=0x0,
per_tuple_context=0x23f8048, instr=0x0, finfo=0x2490db0,
rel=0x7f4116fdc010, event=0x2492ec0) at trigger.c:3640
#15 afterTriggerInvokeEvents (events=events@entry=0xc027f0 <afterTriggers+16>,
firing_id=1, estate=0x2490a80, estate@entry=0x0,
delete_ok=delete_ok@entry=1 '\001') at trigger.c:3831
#16 0x000000000059fc80 in AfterTriggerFireDeferred () at trigger.c:4063
#17 0x00000000004d3385 in CommitTransaction () at xact.c:1969
#18 0x00000000004d42a5 in CommitTransactionCommand () at xact.c:2881
#19 0x00000000006b9925 in finish_xact_command () at postgres.c:2457
#20 0x00000000006bd262 in finish_xact_command () at postgres.c:2448
#21 exec_simple_query (query_string=0x2442f20 "COMMIT TRANSACTION") at
postgres.c:1111
#22 PostgresMain (argc=<optimized out>, argv=argv@entry=0x23ed378,
dbname=0x23ed258 "test", username=<optimized out>) at postgres.c:4076
#23 0x000000000046b5fe in BackendRun (port=0x23eaf00) at postmaster.c:4271
#24 BackendStartup (port=0x23eaf00) at postmaster.c:3945
#25 ServerLoop () at postmaster.c:1701
#26 0x0000000000663246 in PostmasterMain (argc=argc@entry=1,
argv=argv@entry=0x23c3140) at postmaster.c:1309
#27 0x000000000046c0fe in main (argc=1, argv=0x23c3140) at main.c:228
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
