一个表无法truncate但是可以rename,这个乍听起来觉得好奇怪,下面模拟该过程。
3个session:
session1执行truncate和rename操作;
session2执行lock表操作;
session3进行监控。
session1:
session2:
session3:
-EOF-
3个session:
session1执行truncate和rename操作;
session2执行lock表操作;
session3进行监控。
session1:
[gpadmin@wx60 contrib]$ psql gtlions
psql (8.2.15)
Type "help" for help.
gtlions=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
name | character varying(200) |
Indexes:
"idxtestid" btree (id)
"idxtestname" btree (name)
Distributed by: (id)
gtlions=# select pg_backend_pid();
pg_backend_pid
----------------
1473
(1 row)
gtlions=# truncate table test;
Cancel request sent
ERROR: relation "test" does not exist
gtlions=# alter table test rename to test1;
ALTER TABLE
session2:
[gpadmin@wx60 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.
gtlions=# select pg_backend_pid();
pg_backend_pid
----------------
1555
(1 row)
gtlions=# begin;
BEGIN
gtlions=# select * from test limit 10;
id | name
-------+------------
19672 | 19672-asfd
19674 | 19674-asfd
19676 | 19676-asfd
19678 | 19678-asfd
19680 | 19680-asfd
19682 | 19682-asfd
19684 | 19684-asfd
19686 | 19686-asfd
19688 | 19688-asfd
19690 | 19690-asfd
(10 rows)
gtlions=# end;
COMMIT
session3:
[gpadmin@wx60 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.
gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid;
locktype | relation | pid | mode | granted | gp_segment_id
----------+----------+-----+------+---------+---------------
(0 rows)
gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid;
locktype | relation | pid | mode | granted | gp_segment_id
---------------+-------------+------+-----------------+---------+---------------
relation | test | 1555 | AccessShareLock | t | -1
relation | idxtestname | 1555 | AccessShareLock | t | -1
transactionid | | 1555 | ExclusiveLock | t | -1
relation | idxtestid | 1555 | AccessShareLock | t | -1
(4 rows)
gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid;
locktype | relation | pid | mode | granted | gp_segment_id
---------------+-------------+------+---------------------+---------+---------------
transactionid | | 1473 | ExclusiveLock | t | -1
relation | test | 1473 | AccessExclusiveLock | f | -1
transactionid | | 1555 | ExclusiveLock | t | -1
relation | idxtestid | 1555 | AccessShareLock | t | -1
relation | idxtestname | 1555 | AccessShareLock | t | -1
relation | test | 1555 | AccessShareLock | t | -1
(6 rows)
-EOF-