From: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
Date: Thu, 21 Apr 2016 09:01:26 +0000
Message-ID: <F84DE43FDACD4C45AA84E2DA016FAE2F1C6A74F0@MX105CL01.corp.emc.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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: <F84DE43FDACD4C45AA84E2DA016FAE2F1C6A74F0@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