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>