TIMECAPSULE TABLE

Description

The TIMECAPSULE TABLE statement restores a table to an earlier state in the event of human or application errors.

The table can flash back to a past point in time, depending on the old data stored in the system. In addition, GaussDB cannot restore a table to an earlier state through DDL operations that has changed the structure of the table.

Precautions

• The TIMECAPSULE TABLE statement can be used to flash back the old data or the data from the recycle bin.
  • TO TIMECAPSULE and TO CSN can flash back a table to an earlier version.
  • The recycle bin records the objects dropped or truncated by running DROP and TRUNCATE. TO BEFORE DROP and TO BEFORE TRUNCATE flash back from the recycle bin.
• The following object types do not support flashback: system catalogs, DFS tables, global temporary tables, local temporary tables, unlogged tables, sequence tables, encrypted tables, and hash bucket tables.
• Flashback is not supported for tables that contain user-defined types.
• After flashback is enabled, tables in the recycle bin can be backed up but cannot be restored.
• Between the flashback point and the current point, a statement (DDL, DCL, or VACUUM FULL) that modifies the table structure or affects physical storage has been executed. Therefore, the flashback fails.

• To run DROP, you must have the CREATE or USAGE permission on the schema to which the junk object belongs, and you must be the owner of the schema or the owner of the junk object.

  To run TRUNCATE, you must have the CREATE or USAGE permission on the schema to which the junk object belongs, and you must be the owner of the schema or the junk object. In addition, you must have the TRUNCATE permission on the junk object.

• Scenarios or tables that do not support DROP or TRUNCATE:
  • Scenario where the recycle bin is disabled (enable_recyclebin is set to off)
  • Scenario where the system is being maintained (xc_maintenance_mode is set to on) or is being upgraded
  • Scenario where multiple objects are deleted (The DROP or TRUNCATE TABLE command is executed to delete multiple objects at the same time.)
  • System catalogs, DFS tables, global temporary tables, local temporary tables, unlogged tables, sequence tables, and hash bucket tables
  • If the object on which the table depends is an external object (for example, a composite object or a user-defined object), the table is physically deleted and is not moved to the recycle bin.
• DROP FLASHBACK constraints:
  You can specify either the original user-defined 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 a 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. To retrieve a table of an earlier version, perform the following steps:

    • 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.

  • During table restoration, the dropped table along with its original base table name is restored. The subobject names of the table remain the same as those in the recycle bin. You can run the DDL command to manually change the names of subobjects as required.
  • If a table has default values that reference sequences and user-defined functions, flashing back the dropped table will succeed, but the default values will not be restored.
  • Similarly, if a table is referenced by views, dropping the table will cascadingly delete the views. Therefore, flashing back the dropped table will succeed, but the views will not be restored.
  • 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).
  • The recyclebin_retention_time parameter specifies the retention period of objects in the recycle bin. The objects will be automatically deleted after the retention period expires.
  • DROP on multiple tables cannot be restored.
  • Restoration is not supported when accounts or schemas are deleted in cascading mode.
  • When an account or schema is deleted, if the schema or schema object exists in the recycle bin, the common deletion fails and the schema or schema needs to be deleted in cascading mode.
• TRUNCATE FLASHBACK constraints:
  • After TRUNCATE FLASHBACK is performed, the statistics remain unchanged and are still displayed as 0. You can modify the statistics by manual ANALYZE during off-peak hours (to reduce the impact on performance).
  • RENAME TO supports only the DROP FLASHBACK operation to specify a new name for the retrieval table, but does not support TRUNCATE FLASHBACK.
  • TRUNCATE FLASHBACK cannot span statements that affect the table structure or physical storage. Otherwise, an error is reported. If a statement (such as DDL, DCL, VACUUM FULL, and partition adding/deleting/splitting/merging) that modifies the table structure or affects physical storages are executed between the flashback point and current point, 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 exist" is displayed when flashback is performed on a table where DDL operations, such as changing namespaces and table names, have been performed.

Prerequisites

• The enable_recyclebin parameter has been enabled to enable the recycle bin. Contact an administrator for details about how to use the parameter.
• 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. Contact an administrator for details about how to use the parameter.

Syntax

TIMECAPSULE TABLE [schema.]table_name TO { CSN expr | TIMESTAMP expr | BEFORE { DROP [RENAME TO table_name] | TRUNCATE } }

Parameters

• schema

  Specifies a schema containing the table to be flashed back. If this parameter is not specified, the current schema is used.

• table_name

  Specifies a table name.

• TO CSN

  Specifies the CSN corresponding to the time point when the table is to be flashed back. expr must be a number representing a valid CSN.

• TO TIMESTAMP

  Specifies a timestamp value corresponding to the point in time to which you want to flash back the table. The result of expr must be a valid past timestamp (convert a string to a time type using the TO_TIMESTAMP function). The table will be flashed back to a time within approximately 3 seconds of the specified timestamp.

  Note: When the flashback point is too old, the old point cannot be obtained because it is recycled. As a result, the flashback fails and the error message "Restore point too old" is displayed.

• TO BEFORE DROP

  Retrieves dropped tables and their subobjects from the recycle bin.

• 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.

Examples

-- Create a schema.
gaussdb=# CREATE SCHEMA tpcds;

-- Delete the tpcds.reason_t2 table.
DROP TABLE IF EXISTS tpcds.reason_t2;
-- Create the tpcds.reason_t2 table.
gaussdb=# CREATE TABLE tpcds.reason_t2
 (
  r_reason_sk    integer,
  r_reason_id    character(16),
  r_reason_desc  character(100)
  )with(storage_type = ustore);
-- Insert records into the tpcds.reason_t2 table.
gaussdb=#  INSERT INTO tpcds.reason_t2 VALUES (1, 'AA', 'reason1'),(2, 'AB', 'reason2'),(3, 'AC', 'reason3');
INSERT 0 3
-- Delete data from the tpcds.reason_t2 table.
gaussdb=#  TRUNCATE TABLE tpcds.reason_t2;
-- Query data in the tpcds.reason_t2 table.
gaussdb=#  select * from tpcds.reason_t2;
 r_reason_sk | r_reason_id | r_reason_desc 
-------------+-------------+---------------
(0 rows)
-- Perform the TRUNCATE FLASHBACK operation.
gaussdb=#  TIMECAPSULE TABLE tpcds.reason_t2 to BEFORE TRUNCATE;
gaussdb=#  select * from tpcds.reason_t2;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                             
-------------+------------------+------------------------------------------------------------------------------------------------------
           1 | AA               | reason1                                                                                             
           2 | AB               | reason2                                                                                             
           3 | AC               | reason3                                                                                             
(3 rows)
-- Delete the tpcds.reason_t2 table.
gaussdb=#  DROP TABLE tpcds.reason_t2;
-- Perform the DROP FLASHBACK operation.
gaussdb=#  TIMECAPSULE TABLE tpcds.reason_t2 to BEFORE DROP;
TimeCapsule Table
-- Perform DROP Flashback to specify a new name for the retrieval table.
gaussdb=#  TIMECAPSULE TABLE tpcds.reason_t2 TO BEFORE DROP rename to reason_t3;
TimeCapsule Table
-- Clear the recycle bin and delete the schema.
gaussdb=# PURGE RECYCLEBIN;
gaussdb=# DROP SCHEMA tpcds CASCADE;