截断表挂
- WBOY原创
- 2016-06-07 16:04:561327浏览
我有一个表gtlions.cannottruncatetable，该表中有零行，我可以从表中删除，但不能截断，挂起语句sql。详细信息如下：###################在会话 1 上：gtlions=# select version();版本----
我有一个表gtlions.cannottruncatetable，这个表中有零行，我可以从表中删除，但不能截断，挂起语句sql。详细信息如下：
##################在会话 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)
根据售后的提示说明做以下检查：
截断表需要对所有段进行独占锁定才能成功完成事务。
似乎某些进程无法获取的段上有锁。
请按照以下步骤操作，并让我知道这是否有助于确定哪些部分是问题点。
识别进程是否已获取段上的所有锁，例如例如 s
从 pg_stat_Activity 中选择 procpid,sess_id,current_query ;
select * from pg_locks where mppsessionid=
第二个查询将告诉它在哪里无法获取锁（例如关系），一旦找到它，您可以使用下面的查询来了解谁在这些段上持有它。
select * from pg_locks where relation=并授予='t';
如果您发现段上有一些孤立进程持有锁，请尝试终止这些进程（避免使用kill -9，因为它会导致 postmaster 重置）
根据上述步骤我答复如下：
谢谢，详细信息：
在会话 1 上，截断表，仍然挂起：
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的位置在爱尔兰，这会估计人家正在休息，只好自己继续摸索：
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应该持有同样级别的锁，按理说不应该成功一个失败。
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn