Thread: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
From
"Venkatesan, Sekhar"
Date:
<div class="WordSection1"><p class="MsoNormal">Hi Folks,<p class="MsoNormal"> <p class="MsoNormal">We are trying to certifyour product with PostgreSQL DB. I am facing a hang like scenario when ALTER TABLE command is executed from the applicationto add a new column in a table.<p class="MsoNormal">This is happening only when Database partitioning is enabledat PostgreSQL.<p class="MsoNormal"> <p class="MsoNormal">I could see exclusive locks between 2 SQL statements andeach one of them is waiting for the other operation to complete.<p class="MsoNormal">Is this the case of deadlock? Ifso, how does PostgreSQL DB handles deadlock? Any common configurations that can be tried to prevent deadlocks?<p class="MsoNormal">Ifit is not deadlock scenario, how to determine what is causing the hang?<p class="MsoNormal">PostgreSQLserver version is 9.4.5<p class="MsoNormal">ODBC driver version is 9.3<p class="MsoNormal">Belowthe SQL query I executed to determine the locking scenario.<p class="MsoNormal"> <p class="MsoNormal">dm_testenv_docbase=#SELECT blocked_locks.pid AS blocked_pid,<p class="MsoNormal"> blocked_activity.usename AS blocked_user,<p class="MsoNormal"> blocking_locks.pid AS blocking_pid,<p class="MsoNormal"> blocking_activity.usename AS blocking_user,<p class="MsoNormal"> blocked_activity.query AS blocked_statement,<p class="MsoNormal"> blocking_activity.query AS current_statement_in_blocking_process<pclass="MsoNormal"> FROM pg_catalog.pg_locks blocked_locks<p class="MsoNormal"> JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid<p class="MsoNormal"> JOIN pg_catalog.pg_locks blocking_locks<p class="MsoNormal"> ON blocking_locks.locktype= blocked_locks.locktype<p class="MsoNormal"> AND blocking_locks.DATABASE IS NOT DISTINCT FROMblocked_locks.DATABASE<p class="MsoNormal"> AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation<pclass="MsoNormal"> AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page<p class="MsoNormal"> AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple<p class="MsoNormal"> ANDblocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid<p class="MsoNormal"> AND blocking_locks.transactionidIS NOT DISTINCT FROM blocked_locks.transactionid<p class="MsoNormal"> AND blocking_locks.classidIS NOT DISTINCT FROM blocked_locks.classid<p class="MsoNormal"> AND blocking_locks.objid ISNOT DISTINCT FROM blocked_locks.objid<p class="MsoNormal"> AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid<pclass="MsoNormal"> AND blocking_locks.pid != blocked_locks.pid<p class="MsoNormal"> <p class="MsoNormal"> JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid<p class="MsoNormal"> WHERE NOT blocked_locks.GRANTED;<p class="MsoNormal">blocked_pid | blocked_user | blocking_pid | blocking_user| blocked_statement<p class="MsoNormal"> | current_statement_in_b<pclass="MsoNormal">locking_process<p class="MsoNormal">-------------+--------------+--------------+---------------+--------------------------------------------------------------------------------------------------<p class="MsoNormal">----------------------------------------------------------------------+---------------------------------------------------------------------------------------<p class="MsoNormal">---------------------------------------------------------------------------------<p class="MsoNormal"><b> 9726 | testenv | 9884 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_svRJ_ WHERE (RJ_.r_object_id=$1 AND RJ_</b><p class="MsoNormal"><b>.r_object_id=SJ_.r_object_id) ORDERBY SJ_.r_object_id,SJ_.i_position | BEGIN; DROP INDEX d_1f00085e80002d00</b><p class="MsoNormal"> 9726 | testenv | 9733 | testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_<p class="MsoNormal">.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position| RELEASE _EXEC_SVP_0x3356750<p class="MsoNormal"> 9726 | testenv | 9902| testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_<pclass="MsoNormal">.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | UPDATE dm_sysobject_s SETr_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2<p class="MsoNormal"> 9726 | testenv | 9790| testenv | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1 AND RJ_<pclass="MsoNormal">.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position | SELECT WB_.r_object_id FROMdm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 A<p class="MsoNormal">ND WB_.i_vstamp=$2 )<p class="MsoNormal"> 9790 | testenv | 9884 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_sWB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs<p class="MsoNormal">tamp=$2 ) | BEGIN; DROP INDEX d_1f00085e80002d00<p class="MsoNormal"> 9790| testenv | 9726 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs<p class="MsoNormal">tamp=$2 ) | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1<pclass="MsoNormal"> AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position<pclass="MsoNormal"> 9790 | testenv | 9733 | testenv | SELECT WB_.r_object_idFROM dm_sysobject_s WB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs<p class="MsoNormal">tamp=$2 ) | RELEASE _EXEC_SVP_0x3356750<p class="MsoNormal"> 9790 | testenv | 9902 | testenv | SELECT WB_.r_object_id FROM dm_sysobject_sWB_ WHERE (WB_.r_object_id=$1 AND WB_.i_vs<p class="MsoNormal">tamp=$2 ) | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHEREr_object_id = $2<p class="MsoNormal"><b> 9884 | testenv | 9726 | testenv | BEGIN; DROP INDEXd_1f00085e80002d00</b><p class="MsoNormal"><b> | SELECT * FROM dm_method_rv SJ_ , dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1</b><pclass="MsoNormal"> AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position<pclass="MsoNormal"> 9884 | testenv | 9733 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00<p class="MsoNormal"> |RELEASE _EXEC_SVP_0x3356750<p class="MsoNormal"> 9884 | testenv | 9902 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00<p class="MsoNormal"> |UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2<p class="MsoNormal"> 9884 | testenv | 9790 | testenv | BEGIN; DROP INDEX d_1f00085e80002d00<p class="MsoNormal"> | SELECT WB_.r_object_id FROM dm_sysobject_s WB_ WHERE(WB_.r_object_id=$1 A<p class="MsoNormal">ND WB_.i_vstamp=$2 )<p class="MsoNormal"> 9902| testenv | 9884 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt + $1 WHERE r_object_id= $2<p class="MsoNormal"> | BEGIN; DROP INDEX d_1f00085e80002d00<p class="MsoNormal"> 9902 | testenv | 9726 | testenv | UPDATE dm_sysobject_sSET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2<p class="MsoNormal"> | SELECT * FROM dm_method_rv SJ_, dm_method_sv RJ_ WHERE (RJ_.r_object_id=$1<p class="MsoNormal"> AND RJ_.r_object_id=SJ_.r_object_id) ORDER BY SJ_.r_object_id,SJ_.i_position<pclass="MsoNormal"> 9902 | testenv | 9733 | testenv | UPDATE dm_sysobject_sSET r_link_cnt = r_link_cnt + $1 WHERE r_object_id = $2<p class="MsoNormal"> | RELEASE _EXEC_SVP_0x3356750<p class="MsoNormal"> 9902 | testenv | 9790 | testenv | UPDATE dm_sysobject_s SET r_link_cnt = r_link_cnt+ $1 WHERE r_object_id = $2<p class="MsoNormal"> | SELECT WB_.r_object_id FROM dm_sysobject_sWB_ WHERE (WB_.r_object_id=$1 A<p class="MsoNormal">ND WB_.i_vstamp=$2 )<p class="MsoNormal"><spanstyle="color:#1F497D"> </span><p class="MsoNormal"><span style="color:#1F497D">As you can see, DBID: 9884 is blocked by ID: 9726 which in turn is blocked by 9884 ID.</span><p class="MsoNormal"><span style="color:#1F497D">Onlyif I kill the DB ID: 9884 that DB locks are cleared.</span><p class="MsoNormal"><span style="color:#1F497D">Anyknown issue like this partitioning is enabled in PostgreSQL DB.</span><p class="MsoNormal"><spanstyle="color:#1F497D"> </span><p class="MsoNormal"><span style="color:#1F497D">Thanks,<br /> Sekhar</span></div>