هذه الصفحة غير متوفرة حاليًا بلغتك المحلية. نحن نعمل جاهدين على إضافة المزيد من اللغات. شاكرين تفهمك ودعمك المستمر لنا.

Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

DROP/TRUNCATE Flashback

Updated on 2024-07-04 GMT+08:00

Context

  • Flashback DROP enables you to restore tables that are dropped by mistake and their auxiliary structures, such as indexes and table constraints, from the recycle bin. Flashback DROP is based on the recycle bin mechanism. You can restore physical table files recorded in the recycle bin to restore dropped tables.
  • Flashback TRUNCATE enables you to restore tables that are truncated by mistake and restore the physical data of the truncated tables and indexes from the recycle bin. Flashback TRUNCATE is based on the recycle bin mechanism. You can restore physical table files recorded in the recycle bin to restore truncated tables.

Prerequisites

  • The enable_recyclebin parameter has been enabled (by modifying the GUC parameter in the postgresql.conf file) to enable the recycle bin. For details, contact the administrator.
  • The recyclebin_retention_time parameter has been set for specifying the retention period of objects in the recycle bin. The objects will be automatically deleted after the retention period expires. For details, contact the administrator.

Syntax

  • Drop a table.
    DROP TABLE table_name [PURGE]
  • Purge objects in the recycle bin.
    PURGE { TABLE { table_name }
    | INDEX { index_name }
    | RECYCLEBIN
    }
  • Flash back a dropped table.
    TIMECAPSULE TABLE { table_name } TO BEFORE DROP [RENAME TO new_tablename]
  • Truncate a table.
    TRUNCATE TABLE { table_name } [ PURGE ]
  • Flash back a truncated table.
    TIMECAPSULE TABLE { table_name } TO BEFORE TRUNCATE

Parameter Description

  • DROP/TRUNCATE TABLE table_name PURGE
    • Purges table data in the recycle bin by default.
  • PURGE RECYCLEBIN
    • Purges objects in the recycle bin.
  • TO BEFORE DROP

    Retrieves dropped tables and their subobjects from the recycle bin.

    You can specify either the original user-specified name of the table or the system-generated name assigned to the object when it was dropped.
    • System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, the database retrieves that specified object. To see the content in your recycle bin, run select * from gs_recyclebin;.
    • If you specify the user-specified name and the recycle bin contains more than one object of that name, the database retrieves the object that was moved to the recycle bin most recently. If you want to retrieve an older version of the table, then do one of these things:

      • Specify the system-generated recycle bin name of the table you want to retrieve.

      • Run the TIMECAPSULE TABLE ... TO BEFORE DROP statement until you retrieve the table you want.

    • When a dropped table is restored, only the base table name is restored, and the names of other subobjects remain the same as those in the recycle bin. You can run the DDL command to manually change the names of subobjects as required.
    • The recycle bin does not support write operations such as DML, DCL, and DDL, and does not support DQL query operations (supported in later versions).
    • Between the flashback point and the current point, a statement has been executed to modify the table structure or to affect the physical structure. Therefore, the flashback fails. The error message "ERROR: The table definition of %s has been changed." is displayed when flashback is performed on a table where DDL operations have been performed. The error message "ERROR: recycle object %s desired does not exis" is displayed when flashback is performed on DDL operations, such as changing namespaces and table names.
    • If the base table has a truncate trigger, the trigger fails when you truncate the target table together. The target table can only be truncated manually.
  • RENAME TO

    Specifies a new name for the table retrieved from the recycle bin.

  • TO BEFORE TRUNCATE

    Flashes back to the point in time before the TRUNCATE operation.

Syntax Example

-- PURGE TABLE table_name; -- 
-- Check the recycle bin.
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)
-- Create the flashtest table.
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
-- Insert data.
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

-- Drop the flashtest table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
-- Check the recycle bin. The deleted table is moved to the recycle bin.
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)
-- Check the flashtest table. The table does not exist.
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
-- Purge the table from the recycle bin.
gaussdb=# PURGE TABLE flashtest;
PURGE TABLE
-- Check the recycle bin. The table is deleted from the recycle bin.
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
-- Create the flashtest table.
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
-- Create the flashtest_index index for the flashtest table.
gaussdb=# create index flashtest_index on flashtest(id);
CREATE INDEX
-- Drop the table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
-- Check the recycle bin.
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 the flashtest_index index.
gaussdb=# PURGE index flashtest_index;
PURGE INDEX
-- Check the recycle bin. The flashtest_index index is deleted from the recycle bin.
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 the recycle bin.
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
-- Check the recycle bin. The recycle bin is cleared.
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
-- Create the flashtest table.
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
-- Insert data.
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

-- Drop the table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
-- Check the recycle bin. The table is moved to the recycle bin.
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)

-- Check the table. The table does not exist.
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
-- Flash back a dropped table.
gaussdb=# timecapsule table flashtest to before drop;
TimeCapsule Table
-- Check the table. The table is restored to the state before the DROP operation.
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

-- Check the recycle bin. The table is deleted from the recycle bin.
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 the table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
-- Check the recycle bin. The table is moved to the recycle bin.
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)

-- Flash back the dropped table. The table name is rcyname in the recycle bin.
gaussdb=# timecapsule table "BIN$31C14EB48DC$9B4E$0==$0" to before drop;
TimeCapsule Table
-- Check the recycle bin. The table is deleted from the recycle bin.
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 the table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
-- Check the recycle bin. The table is moved to the recycle bin.
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)

-- Check the table. The table does not exist.
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
-- Flash back the dropped table and rename the table.
gaussdb=# timecapsule table flashtest to before drop rename to flashtest_rename;
TimeCapsule Table
-- Check the original table. The table does not exist.
gaussdb=# select * from flashtest;
ERROR:  relation "flashtest" does not exist
LINE 1: select * from flashtest;
                      ^
-- Check the renamed table. The table exists.
gaussdb=# select * from flashtest_rename;
 id | name 
----+------
  1 | A
(1 row)

-- Check the recycle bin. The table is deleted from the recycle bin.
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 a table.
gaussdb=# drop table if EXISTS flashtest_rename;
DROP TABLE
-- Clear the recycle bin.
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
-- Check the recycle bin. The recycle bin is cleared.
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
-- Create the flashtest table.
gaussdb=# create table if not EXISTS flashtest(id int, name text) with (storage_type = ustore);
CREATE TABLE
-- Insert data.
gaussdb=# insert into flashtest values(1, 'A');
INSERT 0 1
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

-- Truncate a table.
gaussdb=# truncate table flashtest;
TRUNCATE TABLE
-- Check the recycle bin. The table data is moved to the recycle bin.
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)

-- Check the table. The table is empty.
gaussdb=# select * from flashtest;
 id | name 
----+------
(0 rows)

-- Flash back a truncated table.
gaussdb=# timecapsule table flashtest to before truncate;
TimeCapsule Table
-- Check the table. The data in the table is restored.
gaussdb=# select * from flashtest;
 id | name 
----+------
  1 | A
(1 row)

-- Check the recycle bin.
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 a table.
gaussdb=# drop table if EXISTS flashtest;
DROP TABLE
-- Clear the recycle bin.
gaussdb=# PURGE RECYCLEBIN;
PURGE RECYCLEBIN
-- Check the recycle bin. The recycle bin is cleared.
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)

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback