TRUNCATETABLEHANG

來源：懂視網責編：小采時間：2020-11-09 14:38:49

TRUNCATETABLEHANG

TRUNCATETABLEHANG: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 ----

推薦度：

點擊下載本文 文檔為doc格式

導讀TRUNCATETABLEHANG: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 ----

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= 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 | 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應是持有同樣級別的鎖，按理來說不應該一個成功一個失敗。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

TRUNCATETABLEHANG

推薦度：

點擊下載本文 文檔為doc格式

標簽： g table truncate

熱門焦點

TRUNCATETABLEHANG

TRUNCATETABLEHANG

TRUNCATETABLEHANG

最新推薦

猜你喜歡

熱門推薦