n | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid |
mppiswriter | gp_segment_id
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------+--------------+
-------------+---------------
relation | 17020 | 11052151 | | | | | | | 1658824590 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
relation | 17020 | 11051470 | | | | | | | 1658824590 | 14027 | ShareLock | t | 113747736 |
t | -1
relation | 17020 | 11051470 | | | | | | | 1658824590 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
relation | 17020 | 11052150 | | | | | | | 1658824590 | 14027 | ShareLock | t | 113747736 |
t | -1
relation | 17020 | 11052150 | | | | | | | 1658824590 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
transactionid | | | | | 1658824590 | | | | 1658824590 | 14027 | ExclusiveLock | t | 113747736 |
t | -1
(6 rows)
根据售后的提示说明做以下检查：
Truncate table would need a execlusive lock on all the segments to make a sucessfull transaction.
It seems like there is a lock on some segments that process cant acquire.
Please follow the below steps and let me know if that helps to identify on which segments is the point of issue.
Idenifity if the process has acquire all the locks on the segments, like for eg.s
select procpid,sess_id,current_query from pg_stat_Activity ;
select * from pg_locks where mppsessionid= and grant='f';
The second query would tell where it has not able to acquire the lock ( like relation ) , once you find it , you can use the query below to know who is holding it on those segments.
select * from pg_locks where relation= and granted='t';
if you find some orphan process on the segments holding locks , try terminating those process ( avoid using kill -9 as it will cause postmaster reset )
根据上述步骤我答复如下：
Thanks, detail info :
On the session 1, truncate the table, still hang:
gtlions=# select pg_backend_pid();
pg_backend_pid
----------------
14027
(1 row)
gtlions=# truncate table gtlions.cannottruncatetable;
Cancel request sent
ERROR: canceling statement due to user request
On the session 2, check the lock info, not find result for the session:
gtlions=# select procpid,sess_id,current_query from pg_stat_activity where procpid=14027;
procpid | sess_id | current_query
---------+-----------+-----------------------------------------------
14027 | 113747736 | truncate table gtlions.cannottruncatetable;
(1 row)
gtlions=# select * from pg_locks where mppsessionid=14027;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid | mppiswriter | gp_segm
ent_id
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-----+------+---------+--------------+-------------+--------
-------
(0 rows)
gtlions=# select * from pg_locks where mppsessionid=14027;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid | mppiswriter | gp_segm
ent_id
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-----+------+---------+--------------+-------------+--------
-------
(0 rows)
Y的，这也太水了吧，我是在搞不清楚mppsessionid怎么会和那个关联起来。由于SR |