TRUNCATETABLEHANG
程序员文章站
2024-02-03 08:00:58
...
I hava a table gtlions.cannottruncatetable, ZERO rows in this table, i can DELETE from the table, but can not TRUNCATE, hang the statement sql. Detail info below: ###################On the session 1: gtlions=# select version(); version ----
I hava a table gtlions.cannottruncatetable, ZERO rows in this table, i can DELETE from the table, but can not TRUNCATE, hang the statement sql.Detail info below:
###################On the session 1:
gtlions=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.15 (Greenplum Database 4.2.5.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 16 2013 23:35:01
(1 row)
gtlions=# \d+ gtlions.cannottruncatetable
Table "gtlions.cannottruncatetable"
Column | Type | Modifiers | Storage | Description
------------------+------------------------+-----------+----------+-------------
host_ip | character varying | | extended |
sys_int_id | numeric | | main |
hostname | character varying | | extended |
prog_name | character varying(300) | | extended |
app_name | character varying | | extended |
app_name_en | character varying | | extended |
app_id | numeric(12,0) | | main |
serverport | numeric(22,0) | | main |
logpath | numeric(22,0) | | main |
log_generall | numeric(22,0) | | main |
log_detail | numeric(22,0) | | main |
transaction_open | numeric(22,0) | | main |
generall_open | numeric(22,0) | | main |
is_use | numeric(22,0) | | main |
id | numeric(22,0) | | main |
logmasterswitch | numeric(22,0) | | main |
process_numb | numeric(22,0) | | main |
process_total | numeric(22,0) | | main |
ips_addr | character varying | | extended |
host_id | numeric(8,0) | | main |
prog_id | numeric(8,0) | | main |
prog_apptypeid | numeric(8,0) | | main |
Has OIDs: no
Distributed by: (app_id)
gtlions=# select count(*) from gtlions.cannottruncatetable;
count
-------
0
(1 row)
gtlions=# select * from pg_class where relname='cannottruncatetable';
relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrel
id | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey |
relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
------------------------+--------------+----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+------------
---+---------------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-
------------+----------------+--------------+--------+------------
cannottruncatetable | 17021 | 11051471 | 17010 | 0 | 23496358 | 0 | 19 | 0 | 11052150 | 0 |
0 | 0 | f | f | r | h | 22 | 0 | 0 | 0 | 0 | 0 | f | f |
f | f | 1558748414 | |
(1 row)
gtlions=# select pg_size_pretty(pg_relation_size('gtlions.cannottruncatetable'));
pg_size_pretty
----------------
608 kB
(1 row)
gtlions=# vacuum analyze gtlions.cannottruncatetable;
VACUUM
gtlions=# select * from pg_class where relname='cannottruncatetable';
relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrel
id | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey |
relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
------------------------+--------------+----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+------------
---+---------------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-
------------+----------------+--------------+--------+------------
cannottruncatetable | 17021 | 11051471 | 17010 | 0 | 23496358 | 0 | 16 | 0 | 11052150 | 0 |
0 | 0 | f | f | r | h | 22 | 0 | 0 | 0 | 0 | 0 | f | f |
f | f | 1558793687 | |
(1 row)
gtlions=# select pg_size_pretty(pg_relation_size('gtlions.cannottruncatetable'));
pg_size_pretty
----------------
512 kB
(1 row)
gtlions=# select pg_backend_pid();
pg_backend_pid
----------------
14027
(1 row)
gtlions=# select now();
now
-------------------------------
2014-10-15 16:52:25.112906+08
(1 row)
gtlions=# truncate table gtlions.cannottruncatetable;
Cancel request sent
ERROR: canceling statement due to user request
gtlions=# select now();
now
-------------------------------
2014-10-15 16:53:39.877717+08
(1 row)
###################On the session 2:
During the session 1 running, open new session 2, check the session 1
gtlions=# select procpid,sess_id,usename,current_query,waiting,age(now(),query_start) from pg_stat_activity where procpid=14027;
procpid | sess_id | usename | current_query | waiting | age
---------+-----------+---------+-----------------------------------------------+---------+-----------------
14027 | 113747736 | gpadmin | truncate table gtlions.cannottruncatetable; | f | 00:00:49.671096
(1 row)
gtlions=# select * from pg_locks where pid=14027;
locktype | database | relation | 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)
gtlions=# select procpid,sess_id,usename,current_query,waiting,age(now(),query_start) from pg_stat_activity where procpid=14027;
procpid | sess_id | usename | current_query | waiting | age
---------+-----------+---------+-----------------------------------------------+---------+-----------------
14027 | 113747736 | gpadmin | truncate table gtlions.cannottruncatetable; | f | 00:01:03.655322
(1 row)
gtlions=# select * from pg_locks where pid=14027;
locktype | database | relation | 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=
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=
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的Location在爱尔兰，这会估计人家正在休息，只好自己继续摸索：
gtlions=# select * from pg_locks where pid=14027;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid |
mppiswriter | gp_segment_id
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------+--------------+
-------------+---------------
relation | 17020 | 11052151 | | | | | | | 1662808322 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
transactionid | | | | | 1662808322 | | | | 1662808322 | 14027 | ExclusiveLock | t | 113747736 |
t | -1
relation | 17020 | 11051470 | | | | | | | 1662808322 | 14027 | ShareLock | t | 113747736 |
t | -1
relation | 17020 | 11051470 | | | | | | | 1662808322 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
relation | 17020 | 11052150 | | | | | | | 1662808322 | 14027 | ShareLock | t | 113747736 |
t | -1
relation | 17020 | 11052150 | | | | | | | 1662808322 | 14027 | AccessExclusiveLock | t | 113747736 |
t | -1
(6 rows)
gtlions=# select * from pg_locks where relation='gtlions.cannottruncatetable'::regclass;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid | mppi
swriter | gp_segment_id
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------+--------------+-----
--------+---------------
relation | 17020 | 11051470 | | | | | | | 1662808322 | 14027 | ShareLock | t | 113747736 | t
| -1
relation | 17020 | 11051470 | | | | | | | 1662808322 | 14027 | AccessExclusiveLock | t | 113747736 | t
| -1
relation | 17020 | 11051470 | | | | | | | 2653373155 | 15567 | AccessExclusiveLock | f | 113747736 | t
| 0
relation | 17020 | 11051470 | | | | | | | 0 | 13773 | AccessShareLock | t | 75284454 | f
| 0
relation | 17020 | 11051470 | | | | | | | 2653366790 | 15569 | AccessExclusiveLock | f | 113747736 | t
| 1
relation | 17020 | 11051470 | | | | | | | 0 | 13789 | AccessShareLock | t | 75284454 | f
| 1
relation | 17020 | 11051470 | | | | | | | 0 | 13807 | AccessShareLock | t | 75284454 | f
| 2
relation | 17020 | 11051470 | | | | | | | 2653175988 | 15572 | AccessExclusiveLock | f | 113747736 | t
| 2
relation | 17020 | 11051470 | | | | | | | 0 | 13830 | AccessShareLock | t | 75284454 | f
| 3
relation | 17020 | 11051470 | | | | | | | 2653198212 | 15577 | AccessExclusiveLock | f | 113747736 | t
| 3
relation | 17020 | 11051470 | | | | | | | 2653197212 | 15583 | ShareLock | t | 113747736 | t
| 4
relation | 17020 | 11051470 | | | | | | | 2653197212 | 15583 | AccessExclusiveLock | t | 113747736 | t
| 4
relation | 17020 | 11051470 | | | | | | | 0 | 13858 | AccessShareLock | t | 75284454 | f
| 5
relation | 17020 | 11051470 | | | | | | | 2653196128 | 15589 | AccessExclusiveLock | f | 113747736 | t
| 5
relation | 17020 | 11051470 | | | | | | | 0 | 13091 | AccessShareLock | t | 75284454 | f
| 6
relation | 17020 | 11051470 | | | | | | | 2653195038 | 16256 | AccessExclusiveLock | f | 113747736 | t
| 6
relation | 17020 | 11051470 | | | | | | | 0 | 13098 | AccessShareLock | t | 75284454 | f
| 7
gtlions=# select * from pg_locks where relation='gtlions.cannottruncatetable'::regclass and pid!=14027;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid | mppi
swriter | gp_segment_id
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------+--------------+-----
--------+---------------
relation | 17020 | 11051470 | | | | | | | 2653373155 | 15567 | AccessExclusiveLock | f | 113747736 | t
| 0
relation | 17020 | 11051470 | | | | | | | 0 | 13773 | AccessShareLock | t | 75284454 | f
| 0
relation | 17020 | 11051470 | | | | | | | 2653366790 | 15569 | AccessExclusiveLock | f | 113747736 | t
| 1
relation | 17020 | 11051470 | | | | | | | 0 | 13789 | AccessShareLock | t | 75284454 | f
| 1
relation | 17020 | 11051470 | | | | | | | 0 | 13807 | AccessShareLock | t | 75284454 | f
| 2
relation | 17020 | 11051470 | | | | | | | 2653175988 | 15572 | AccessExclusiveLock | f | 113747736 | t
| 2
relation | 17020 | 11051470 | | | | | | | 0 | 13830 | AccessShareLock | t | 75284454 | f
| 3
relation | 17020 | 11051470 | | | | | | | 2653198212 | 15577 | AccessExclusiveLock | f | 113747736 | t
| 3
relation | 17020 | 11051470 | | | | | | | 2653197212 | 15583 | ShareLock | t | 113747736 | t
| 4
relation | 17020 | 11051470 | | | | | | | 2653197212 | 15583 | AccessExclusiveLock | t | 113747736 | t
| 4
relation | 17020 | 11051470 | | | | | | | 0 | 13858 | AccessShareLock | t | 75284454 | f
| 5
relation | 17020 | 11051470 | | | | | | | 2653196128 | 15589 | AccessExclusiveLock | f | 113747736 | t
| 5
relation | 17020 | 11051470 | | | | | | | 0 | 13091 | AccessShareLock | t | 75284454 | f
| 6
relation | 17020 | 11051470 | | | | | | | 2653195038 | 16256 | AccessExclusiveLock | f | 113747736 | t
| 6
relation | 17020 | 11051470 | | | | | | | 0 | 13098 | AccessShareLock | t | 75284454 | f
| 7
relation | 17020 | 11051470 | | | | | | | 2653223811 | 16258 | AccessExclusiveLock | f | 113747736 | t
| 7
relation | 17020 | 11051470 | | | | | | | 2653164802 | 16261 | ShareLock | t | 113747736 | t
| 8
relation | 17020 | 11051470 | | | | | | | 2653164802 | 16261 | AccessExclusiveLock | t | 113747736 | t
| 8
relation | 17020 | 11051470 | | | | | | | 2653228628 | 16266 | AccessExclusiveLock | f | 113747736 | t
| 9
relation | 17020 | 11051470 | | | | | | | 0 | 13118 | AccessShareLock | t | 75284454 | f
| 9
relation | 17020 | 11051470 | | | | | | | 2653420396 | 16271 | AccessExclusiveLock | f | 113747736 | t
| 10
relation | 17020 | 11051470 | | | | | | | 0 | 13135 | AccessShareLock | t | 75284454 | f
| 10
relation | 17020 | 11051470 | | | | | | | 2653180874 | 16277 | AccessExclusiveLock | f | 113747736 | t
| 11
relation | 17020 | 11051470 | | | | | | | 0 | 13146 | AccessShareLock | t | 75284454 | f
| 11
relation | 17020 | 11051470 | | | | | | | 2653191613 | 8822 | ShareLock | t | 113747736 | t
| 12
relation | 17020 | 11051470 | | | | | | | 2653191613 | 8822 | AccessExclusiveLock | t | 113747736 | t
| 12
relation | 17020 | 11051470 | | | | | | | 2653137608 | 8824 | ShareLock | t | 113747736 | t
| 13
relation | 17020 | 11051470 | | | | | | | 2653137608 | 8824 | AccessExclusiveLock | t | 113747736 | t
| 13
relation | 17020 | 11051470 | | | | | | | 2653170505 | 8827 | AccessExclusiveLock | f | 113747736 | t
| 14
relation | 17020 | 11051470 | | | | | | | 0 | 19567 | AccessShareLock | t | 75284454 | f
| 14
relation | 17020 | 11051470 | | | | | | | 2653146597 | 8832 | ShareLock | t | 113747736 | t
| 15
relation | 17020 | 11051470 | | | | | | | 2653146597 | 8832 | AccessExclusiveLock | t | 113747736 | t
| 15
relation | 17020 | 11051470 | | | | | | | 2653166445 | 8838 | AccessExclusiveLock | f | 113747736 | t
| 16
relation | 17020 | 11051470 | | | | | | | 0 | 19593 | AccessShareLock | t | 75284454 | f
| 16
relation | 17020 | 11051470 | | | | | | | 2653165327 | 8844 | ShareLock | t | 113747736 | t
| 17
relation | 17020 | 11051470 | | | | | | | 2653165327 | 8844 | AccessExclusiveLock | t | 113747736 | t
| 17
relation | 17020 | 11051470 | | | | | | | 2653219764 | 11121 | ShareLock | t | 113747736 | t
| 18
relation | 17020 | 11051470 | | | | | | | 2653219764 | 11121 | AccessExclusiveLock | t | 113747736 | t
| 18
relation | 17020 | 11051470 | | | | | | | 2653227486 | 11123 | AccessExclusiveLock | f | 113747736 | t
| 19
relation | 17020 | 11051470 | | | | | | | 0 | 15309 | AccessShareLock | t | 75284454 | f
| 19
relation | 17020 | 11051470 | | | | | | | 2653155802 | 11125 | AccessExclusiveLock | f | 113747736 | t
| 20
relation | 17020 | 11051470 | | | | | | | 0 | 15320 | AccessShareLock | t | 75284454 | f
| 20
relation | 17020 | 11051470 | | | | | | | 0 | 15330 | AccessShareLock | t | 75284454 | f
| 21
relation | 17020 | 11051470 | | | | | | | 2653185053 | 11131 | AccessExclusiveLock | f | 113747736 | t
| 21
relation | 17020 | 11051470 | | | | | | | 2653157522 | 11137 | AccessExclusiveLock | f | 113747736 | t
| 22
relation | 17020 | 11051470 | | | | | | | 0 | 15341 | AccessShareLock | t | 75284454 | f
| 22
relation | 17020 | 11051470 | | | | | | | 2653151279 | 11143 | ShareLock | t | 113747736 | t
| 23
relation | 17020 | 11051470 | | | | | | | 2653151279 | 11143 | AccessExclusiveLock | t | 113747736 | t
| 23
(48 rows)
gtlions=# select distinct pid from pg_locks where relation='gtlions.cannottruncatetable'::regclass and pid!=14027;
pid
-------
8822
8824
8827
8832
8838
8844
11121
11123
11125
11131
11137
11143
13091
13098
13118
13135
13146
13773
13789
13807
13830
13858
15309
15320
15330
15341
15567
15569
15572
15577
15583
15589
16256
16258
16261
16266
16271
16277
19567
19593
(40 rows)
gtlions=# select procpid,sess_id,current_query,query_start, backend_start,client_addr,application_name from pg_Stat_activity where procpid in (select distinct pid from pg_locks where relation='gtlions.cannottruncatetable'::r
gtlions.b-# ;
procpid | sess_id | current_query | query_start | backend_start | client_addr | application_name
---------+---------+---------------+-------------+---------------+-------------+------------------
(0 rows)
gtlions=# select procpid,sess_id,current_query,query_start, backend_start,client_addr,application_name from pg_Stat_activity where procpid in (select distinct pid from pg_locks where relation='gtlions.cannottruncatetable'::regclass and pid!=14027); procpid | sess_id | current_query | query_start | backend_start | client_addr | application_name
---------+---------+---------------+-------------+---------------+-------------+------------------
(0 rows)
终于发现了点异常情况，这个对象被不存在的会话进程锁住了,顿时觉得有希望了。午饭过后发现数据库被重启了，我F**K。。。。。自然而然再次执行truncate是没有问题了，白白丢失了一次可以继续摸索的机会。
附：还有的疑惑就是，之前曾经使用alter table rename to 是没有问题的，而这个命令和truncate应是持有同样级别的锁，按理来说不应该一个成功一个失败。
推荐阅读