TRUNCATETABLEHANG_懂视_懂你更懂生活

TRUNCATETABLEHANG

2020-11-09 14:38:49 责编:小采

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_-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= 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的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 | 137 | 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 | 155 | 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 | 137 | 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 | 155 | 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 | | | | | | | 26531802 | 16261 | ShareLock | t | 113747736 | t 
 | 8
 relation | 17020 | 11051470 | | | | | | | 26531802 | 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 | | | | | | | 26531645 | 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 | | | | | | | 26532197 | 11121 | ShareLock | t | 113747736 | t 
 | 18
 relation | 17020 | 11051470 | | | | | | | 26532197 | 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
 137
 13807
 13830
 13858
 15309
 15320
 15330
 15341
 15567
 15569
 15572
 15577
 15583
 155
 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应是持有同样级别的锁，按理来说不应该一个成功一个失败。

下载本文

显示全文

全部频道