PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled. - Mailing list pgsql-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