云数据库 GAUSSDB-闪回DROP/TRUNCATE:语法示例

时间:2023-11-24 09:43:09

语法示例

-- PURGE TABLE table_name; -- 
--查看回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)

gaussdb=# drop table if EXISTS flashtest;
NOTICE:  table "flashtest" does not exist, skipping
DROP TABLE
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)
--创建表flashtest
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
--插入数据
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--DROP表flashtest
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
--查看回收站,删除的表被放入回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18591 |   12737 |    18585 | BIN$31C14EB4899$9737$0==$0   | flashtest            | d            |       0 |      79352606 | 2023-09-13 20:01:28.640664+08 |     79352595 |     7935259
5 |         2200 |       10 |             0 |          18585 | t             | t           | 225492       |         225492
     18591 |   12737 |    18590 | BIN$31C14EB489E$12D1B978==$0 | pg_toast_18585_index | d            |       3 |      79352606 | 2023-09-13 20:01:28.64093+08  |     79352595 |     7935259
5 |           99 |       10 |             0 |          18590 | f             | f           | 0            |              0
     18591 |   12737 |    18588 | BIN$31C14EB489C$12D1BF60==$0 | pg_toast_18585       | d            |       2 |      79352606 | 2023-09-13 20:01:28.641018+08 |            0 |            
0 |           99 |       10 |             0 |          18588 | f             | f           | 225492       |         225492
(3 rows)
--查看表flashtest,表不存在
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
--PURGE表,将回收站中的表删除
gaussdb=# PURGE TABLE flashtest;
PURGE TABLE
--查看回收站,回收站中的表被删除
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)


-- PURGE INDEX index_name; --
gaussdb=# drop table if EXISTS flashtest;
NOTICE:  table "flashtest" does not exist, skipping
DROP TABLE
--创建表flashtest
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
--为表flashtest创建索引flashtest_index
gaussdb=# create index flashtest_index on flashtest(id);
CREATE INDEX
--DROP表
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
--查看回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18648 |   12737 |    18641 | BIN$31C14EB48D1$9A85$0==$0   | flashtest            | d            |       0 |      79354509 | 2023-09-13 20:40:11.360638+08 |     79354506 |     7935450
8 |         2200 |       10 |             0 |          18641 | t             | t           | 226642       |         226642
     18648 |   12737 |    18646 | BIN$31C14EB48D6$12E230B8==$0 | pg_toast_18641_index | d            |       3 |      79354509 | 2023-09-13 20:40:11.361034+08 |     79354506 |     7935450
6 |           99 |       10 |             0 |          18646 | f             | f           | 0            |              0
     18648 |   12737 |    18644 | BIN$31C14EB48D4$12E236A0==$0 | pg_toast_18641       | d            |       2 |      79354509 | 2023-09-13 20:40:11.36112+08  |            0 |            
0 |           99 |       10 |             0 |          18644 | f             | f           | 226642       |         226642
     18648 |   12737 |    18647 | BIN$31C14EB48D7$9A85$0==$0   | flashtest_index      | d            |       1 |      79354509 | 2023-09-13 20:40:11.361246+08 |     79354508 |     7935450
8 |         2200 |       10 |             0 |          18647 | f             | t           | 0            |              0
(4 rows)

--PURGE索引flashtest_index
gaussdb=# PURGE index flashtest_index;
PURGE INDEX
--查看回收站,回收站中的索引flashtest_index被删除
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18648 |   12737 |    18641 | BIN$31C14EB48D1$9A85$0==$0   | flashtest            | d            |       0 |      79354509 | 2023-09-13 20:40:11.360638+08 |     79354506 |     7935450
8 |         2200 |       10 |             0 |          18641 | t             | t           | 226642       |         226642
     18648 |   12737 |    18646 | BIN$31C14EB48D6$12E230B8==$0 | pg_toast_18641_index | d            |       3 |      79354509 | 2023-09-13 20:40:11.361034+08 |     79354506 |     7935450
6 |           99 |       10 |             0 |          18646 | f             | f           | 0            |              0
     18648 |   12737 |    18644 | BIN$31C14EB48D4$12E236A0==$0 | pg_toast_18641       | d            |       2 |      79354509 | 2023-09-13 20:40:11.36112+08  |            0 |            
0 |           99 |       10 |             0 |          18644 | f             | f           | 226642       |         226642
(3 rows)

-- PURGE RECYCLEBIN --
--PURGE回收站
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
--查看回收站,回收站被清空
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)

-- TIMECAPSULE TABLE { table_name } TO BEFORE DROP [RENAME TO new_tablename] --
gaussdb=# drop table if EXISTS flashtest;
NOTICE:  table "flashtest" does not exist, skipping
DROP TABLE
--创建表flashtest
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
--插入数据
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--DROP表
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
--查看回收站,表被放入回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18658 |   12737 |    18652 | BIN$31C14EB48DC$9B2B$0==$0   | flashtest            | d            |       0 |      79354760 | 2023-09-13 20:47:57.075907+08 |     79354753 |     7935475
3 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824
     18658 |   12737 |    18657 | BIN$31C14EB48E1$12E45E00==$0 | pg_toast_18652_index | d            |       3 |      79354760 | 2023-09-13 20:47:57.076129+08 |     79354753 |     7935475
3 |           99 |       10 |             0 |          18657 | f             | f           | 0            |              0
     18658 |   12737 |    18655 | BIN$31C14EB48DF$12E46400==$0 | pg_toast_18652       | d            |       2 |      79354760 | 2023-09-13 20:47:57.07621+08  |            0 |            
0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824
(3 rows)

--查看表,表不存在
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
--闪回drop表
gaussdb=# timecapsule table flashtest to before drop;
TimeCapsule Table
--查看表,表被恢复到drop之前
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--查看回收站,回收站中的表被删除
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)

--DROP表
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
--查看回收站,表被放入回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |        rcyoriginname         | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcy
changecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+------------------------------+--------------+---------+---------------+-------------------------------+--------------+----
----------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18664 |   12737 |    18652 | BIN$31C14EB48DC$9B4E$0==$0   | flashtest                    | d            |       0 |      79354845 | 2023-09-13 20:49:17.762977+08 |     79354753 |    
 79354753 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824
     18664 |   12737 |    18657 | BIN$31C14EB48E1$12E680A8==$0 | BIN$31C14EB48E1$12E45E00==$0 | d            |       3 |      79354845 | 2023-09-13 20:49:17.763271+08 |     79354753 |    
 79354753 |           99 |       10 |             0 |          18657 | f             | f           | 0            |              0
     18664 |   12737 |    18655 | BIN$31C14EB48DF$12E68698==$0 | BIN$31C14EB48DF$12E46400==$0 | d            |       2 |      79354845 | 2023-09-13 20:49:17.763343+08 |            0 |    
        0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824
(3 rows)

--闪回drop表,表名用回收站中的rcyname
gaussdb=# timecapsule table "BIN$31C14EB48DC$9B4E$0==$0" to before drop;
TimeCapsule Table
--查看回收站,回收站中的表被删除
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)

gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--DROP表
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
--查看回收站,表被放入回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |        rcyoriginname         | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcy
changecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+------------------------------+--------------+---------+---------------+-------------------------------+--------------+----
----------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18667 |   12737 |    18652 | BIN$31C14EB48DC$9B8D$0==$0   | flashtest                    | d            |       0 |      79354943 | 2023-09-13 20:52:14.525946+08 |     79354753 |    
 79354753 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824
     18667 |   12737 |    18657 | BIN$31C14EB48E1$1320B4F0==$0 | BIN$31C14EB48E1$12E680A8==$0 | d            |       3 |      79354943 | 2023-09-13 20:52:14.526319+08 |     79354753 |    
 79354753 |           99 |       10 |             0 |          18657 | f             | f           | 0            |              0
     18667 |   12737 |    18655 | BIN$31C14EB48DF$1320BAE0==$0 | BIN$31C14EB48DF$12E68698==$0 | d            |       2 |      79354943 | 2023-09-13 20:52:14.526423+08 |            0 |    
        0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824
(3 rows)

--查看表,表不存在
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
--闪回drop表,并重命名表
gaussdb=# timecapsule table flashtest to before drop rename to flashtest_rename;
TimeCapsule Table
--查看原表,表不存在
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
--查看重命名后的表,表存在
gaussdb=# select * from flashtest_rename;
 id | name 
----+------
  1 | A
(1 row)

--查看回收站,回收站中的表被删除
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)
--drop表
gaussdb=# drop table if EXISTS flashtest_rename;
DROP TABLE
--清空回收站
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
--查看回收站,回收站被清空
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)


-- TIMECAPSULE TABLE { table_name } TO BEFORE TRUNCATE --
gaussdb=# drop table if EXISTS flashtest;
NOTICE:  table "flashtest" does not exist, skipping
DROP TABLE
--创建表flashtest
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
--插入数据
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--truncate表
gaussdb=# truncate table flashtest;
TRUNCATE TABLE
--查看回收站,表的数据被放入回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18703 |   12737 |    18697 | BIN$31C14EB4909$9E4C$0==$0   | flashtest            | t            |       0 |      79356608 | 2023-09-13 21:24:42.819863+08 |     79356606 |     7935660
6 |         2200 |       10 |             0 |          18697 | t             | t           | 227927       |         227927
     18703 |   12737 |    18700 | BIN$31C14EB490C$132FE3F0==$0 | pg_toast_18697       | t            |       2 |      79356608 | 2023-09-13 21:24:42.820358+08 |            0 |            
0 |           99 |       10 |             0 |          18700 | f             | f           | 227927       |         227927
     18703 |   12737 |    18702 | BIN$31C14EB490E$132FEA40==$0 | pg_toast_18697_index | t            |       3 |      79356608 | 2023-09-13 21:24:42.821012+08 |     79356606 |     7935660
6 |           99 |       10 |             0 |          18702 | f             | f           | 0            |              0
(3 rows)

--查看表,表中的数据为空
gaussdb=# select * from flashtest;
 id | name 
----+------
(0 rows)

--闪回truncate表
gaussdb=# timecapsule table flashtest to before truncate;
TimeCapsule Table
--查看表,表中的数据被恢复
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

--查看回收站
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecs
n | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+------------
--+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------
     18703 |   12737 |    18702 | BIN$31C14EB490E$132FFC38==$0 | pg_toast_18697_index | t            |       3 |      79356610 | 2023-09-13 21:24:42.872654+08 |     79356606 |     7935660
6 |           99 |       10 |             0 |          18708 | f             | f           | 0            |              0
     18703 |   12737 |    18700 | BIN$31C14EB490C$13300228==$0 | pg_toast_18697       | t            |       2 |      79356610 | 2023-09-13 21:24:42.872732+08 |            0 |            
0 |           99 |       10 |             0 |          18706 | f             | f           | 0            |         227928
     18703 |   12737 |    18697 | BIN$31C14EB4909$9E4D$0==$0   | flashtest            | t            |       0 |      79356610 | 2023-09-13 21:24:42.872792+08 |     79356606 |     7935660
6 |         2200 |       10 |             0 |          18704 | t             | t           | 0            |         227928
(3 rows)

--drop表
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
--清空回收站
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
--查看回收站,回收站被清空
gaussdb=# select * from gs_recyclebin;
 rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace
 | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 
-----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+--------------
-+----------------+---------------+-------------+--------------+----------------
(0 rows)
support.huaweicloud.com/fg-gaussdb-cent/gaussdb-48-0159.html