• Can rename table but can not truncate table


    一个表无法truncate可是能够rename,这个乍听起来认为好奇怪,以下模拟该过程。
    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-
  • 相关阅读:
    C# 五子棋_GDI+实现
    SAE python+chrome扩展快速存储喜欢的图片(可做图床)
    C# 生成迷宫及寻路
    解决 yii2 从数据库查出来的数据都变成了字符串格式
    英语词根3
    “诸葛马前课-小六壬”全面解析
    Ubuntu 尝试
    ListView + ToolTip 的問題
    英语词根 1
    英语词根6
  • 原文地址:https://www.cnblogs.com/yxysuanfa/p/6794969.html
Copyright © 2020-2023  润新知