Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled. - Mailing list pgsql-sql

For DROP_INDEX case, adding the lock_timeout setting to few seconds helps in clearing the DB locks and the drop index operation to complete.

For ALTER table hang issue, none of the DB timeout settings are helping.

Need your assistance to identify if there is any known issue when data partitioning is enabled in PostgreSQL DB.

Thanks,
Sekhar

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

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