From: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
Date: Thu, 21 Apr 2016 09:08:52 +0000
Message-ID: <F84DE43FDACD4C45AA84E2DA016FAE2F1C6A7500@MX105CL01.corp.emc.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
I attached the DB locking scenario for DROP INDEX call. Same scenario is seen for ALTER TABLE too.
Here is the locking information from the DB:
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement
| current_statement_in_b
locking_process
-------------+--------------+--------------+---------------+--------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
31761 | testenv | 31886 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | RELEASE _EXEC_SVP_0x4186740
31761 | testenv | 31907 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
31761 | testenv | 31902 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
31761 | testenv | 31827 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
31827 | testenv | 31886 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | RELEASE _EXEC_SVP_0x4186740
31827 | testenv | 31907 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
31827 | testenv | 31761 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
31827 | testenv | 31902 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
31902 | testenv | 31886 | testenv | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
| RELEASE _EXEC_SVP_0x4186740
31902 | testenv | 31907 | testenv | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
| UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
31902 | testenv | 31761 | testenv | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
| SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
31902 | testenv | 31827 | testenv | BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
| SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
31907 | testenv | 31886 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| RELEASE _EXEC_SVP_0x4186740
31907 | testenv | 31761 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
31907 | testenv | 31902 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| BEGIN; ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL
31907 | testenv | 31827 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
(16 rows)
dm_testenv_docbase=#
I got the pstack of the hanging process and is as below:
[d73@centos71 log]$ pstack 31885
#0 0x00007f62aec40b7d in poll () from /lib64/libc.so.6
#1 0x00007f62a9e97629 in SOCK_wait_for_ready (sock=0x40eee60, output=0, retry_count=1) at socket.c:529
#2 0x00007f62a9e97ae1 in SOCK_get_next_byte (self=0x40eee60, peek=0) at socket.c:946
#3 0x00007f62a9e980a9 in SOCK_get_id (self=0x40eee60) at socket.c:701
#4 0x00007f62a9e70a31 in CC_send_query_append (self=0x427cc60, query=0x3e8fad0 " ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL ", qi=0x0, flag=4, stmt=0x4273c50, appendq=<optimized out>) at connection.c:2956
#5 0x00007f62a9ea1e7f in SC_execute (self=0x4273c50) at statement.c:2068
#6 0x00007f62a9e82536 in Exec_with_parameters_resolved (stmt=0x4273c50, exec_end=0x7fff02fd7548) at execute.c:519
#7 0x00007f62a9e83f50 in PGAPI_Execute (hstmt=0x4273c50, flag=<optimized out>) at execute.c:1164
#8 0x00007f62a9e8497c in PGAPI_ExecDirect (hstmt=0x4273c50, szSqlStr=0x4247f10 " ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL ", cbSqlStr=70, flag=<optimized out>) at execute.c:202
#9 0x00007f62a9eab9a9 in SQLExecDirect (StatementHandle=0x4273c50, StatementText=0x4247f10 " ALTER TABLE my_ptype_829847_s ADD i_sharing_type varchar(16) NULL ", TextLength=70) at odbcapi.c:365
#10 0x00007f62af74aed2 in SQLExecDirect () from /lib64/libodbc.so.2
#11 0x0000000000b86600 in DBConnection::ExecSQL(DBString const&, long*, DBBlobWriter*, DBDataType, char**) ()
#12 0x0000000000b86beb in DBConnection::ExecDDL(DBString const&, long*) ()
#13 0x0000000000b9dd61 in DBTableImp::AddColumn(DBTable&, DBRowDef const&) ()
#14 0x00000000005d24c2 in dmPTManager::AddAttributes(dmType*, int, dmAttrInfo**, dmBool, dmBool, dmBool, dmBool) ()
#15 0x00000000005d416f in dmPTManager::AddAndSaveShareableTypeAttrs(dmType*, dmBool, dmBool) ()
#16 0x00000000005d43f1 in dmPTManager::SetShareableType(Dtyp_query_context*, dmType*, dmBool, dmBool) ()
#17 0x00000000007b334d in RunAndFinalize(dmObject*, dmSession*, dmID&, dmBool) ()
#18 0x00000000007b79b8 in dmMigrateToLite(char*, dmID, dmObject*, dmSession*) ()
#19 0x0000000000547d08 in dmSession::Apply(int, dmID, dmObject*, int, dmBool) ()
#20 0x000000000047f83d in DoApply(dmSessionContext*, int, dmID, char*, dmBool) ()
#21 0x0000000000481a79 in ApplyInternal(dmID, int, dmID, char*, int*) ()
#22 0x00000000004ac091 in Apply_string(char*, int, char*, char*, int*, int*, int*, int*) ()
#23 0x00000000004a1ed5 in iIXApply_string(void*) ()
#24 0x0000000000ba8fc6 in _nwvss_(unsigned int, dscp_t*, int, rpc_t*, int, int (*)(...), int (*)(...), int (*)(...), void*, _rpcctl*) (_f_=12, _di_=0x7fff02fd9c40, _ci_=126, _rp_=0x7fff02fd9b70, _id_=54, _unpack_=0x4a4582 <iUSApply_string(rpc_t*, _Svrpc*, void*)>, _pack_=0x4a6940 <iPRApply_string(rpc_t*, _Rrpc, void*)>, _call_=0x4a1e9a <iIXApply_string(void*)>, _args_=0x7fff02fd9a10, _desc_=0x7fff02fd9a60) at ../../../src/lib/common/nwvss.c:160
#25 0x00000000004a07cc in S_Apply_string(int, rpc_t*, _Svrpc, dscp_t*) ()
#26 0x000000000049d17f in driver_Proc(int, dscp_t*) ()
#27 0x0000000000498f6a in dmNetwiseConnection::Dispatch(dmBool&) ()
#28 0x00000000004b3783 in dmSessionThreadStart(dmExecutionContext*, void*) ()
#29 0x00000000009c9f0e in dmFork::Launch() ()
#30 0x00000000004b29cb in dmServerExecutionContextPool::ProcessRequests() ()
#31 0x00000000004b30f4 in rpc_main(char const*, dmServiceFailoverList&, dmSession*) ()
#32 0x00000000004854b2 in dmRPCInit(char const*, char const*, int, char const*, dmServiceFailoverList&, dmObject&) ()
#33 0x0000000000488587 in dmRPCMain(int, char const**, dmBool, dmBool) ()
#34 0x0000000000489d1d in main ()
Let me know if you can get anything from the details attached. Let me know I need to collect more information for this.
Thanks,
Sekhar
From: Venkatesan, Sekhar
Sent: Thursday, April 21, 2016 2:31 PM
To: pgsql-sql@postgresql.org
Subject: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
Hi Folks,
We are trying to certify our product with PostgreSQL DB. I am facing a hang like scenario when ALTER TABLE command is executed from the application to add a new column in a table.
This is happening only when Database partitioning is enabled at PostgreSQL.
I could see exclusive locks between 2 SQL statements and each one of them is waiting for the other operation to complete.
Is this the case of deadlock? If so, how does PostgreSQL DB handles deadlock? Any common configurations that can be tried to prevent deadlocks?
If it is not deadlock scenario, how to determine what is causing the hang?
PostgreSQL server version is 9.4.5
ODBC driver version is 9.3
Below the SQL query I executed to determine the locking scenario.
dm_testenv_docbase=# SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement
| current_statement_in_b
locking_process
-------------+--------------+--------------+---------------+--------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
9726 | testenv | 9884 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | BEGIN; DROP INDEX d_1f00085e80002d00
9726 | testenv | 9733 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | RELEASE _EXEC_SVP_0x3356750
9726 | testenv | 9902 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
9726 | testenv | 9790 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_
.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
9790 | testenv | 9884 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | BEGIN; DROP INDEX d_1f00085e80002d00
9790 | testenv | 9726 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
9790 | testenv | 9733 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | RELEASE _EXEC_SVP_0x3356750
9790 | testenv | 9902 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs
tamp=$2 ) | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
9884 | testenv | 9726 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00
| SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
9884 | testenv | 9733 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00
| RELEASE _EXEC_SVP_0x3356750
9884 | testenv | 9902 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00
| UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
9884 | testenv | 9790 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00
| SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
9902 | testenv | 9884 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| BEGIN; DROP INDEX d_1f00085e80002d00
9902 | testenv | 9726 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1
AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position
9902 | testenv | 9733 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| RELEASE _EXEC_SVP_0x3356750
9902 | testenv | 9790 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2
| SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A
ND WB_.i_vstamp=$2 )
As you can see, DB ID: 9884 is blocked by ID: 9726 which in turn is blocked by 9884 ID.
Only if I kill the DB ID: 9884 that DB locks are cleared.
Any known issue like this partitioning is enabled in PostgreSQL DB.
Thanks,
Sekhar
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sekhar.venkatesan@emc.com
Subject: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
In-Reply-To: <F84DE43FDACD4C45AA84E2DA016FAE2F1C6A7500@MX105CL01.corp.emc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox