Table recycle bin

Example 1: Delete and recover a table

1. Create a standard table and delete it.

   CREATE TABLE tbl1 (
     id INT NOT NULL)
   WITH (
       orientation = 'column',
       distribution_key = 'id',
       clustering_key = 'id',
       event_time_column = 'id');
   INSERT INTO tbl1 SELECT i  FROM GENERATE_SERIES(1, 1000000) i;
   DROP TABLE tbl1;

2. Check the recycle bin to confirm that the table has been moved to it.

   SELECT * FROM hologres.hg_recyclebin;

   The following result is returned:

   table_id | schema_name | table_name |   table_owner   |    dropby   |   drop_time        
   ---------+-------------+------------+-----------------+-------------+-----------------------
          14| public      | tbl1       | xx_developer  | 1365xxxxxxxx| 2025-04-17 19:23:10+08
   (1 row)

   Check the table's storage:

   SELECT (hologres.hg_recyclebin_relation_size('tbl1')/1024)::text||'KB' AS hg_recyclebin_relation_size;

   The command returns the storage size of the table:

   hg_recyclebin_relation_size 
   -----------------------------
                         1336KB
   (1 row)

3. Recover the table.

   -- Recover all data and properties of the standard table tbl1, including its primary key and clustering key.
   RECOVER TABLE tbl1;  

Example 2: Delete and recover a partitioned table

1. Create a partitioned table with the corresponding parent and child tables.

   CREATE TABLE tbl2_parent(id INT) PARTITION BY list (id);
   CREATE TABLE tbl2_child_1 PARTITION OF tbl2_parent FOR VALUES IN (1);
   CREATE TABLE tbl2_child_2 PARTITION OF tbl2_parent FOR VALUES IN (2);

2. Delete one of the child tables and recover it. The recovered child table becomes a standard table and must be manually attached to the original parent table.

   -- Delete a child table.
   DROP TABLE tbl2_child_1;
   
   -- The child table is moved to the recycle bin.
   SELECT * FROM hologres.hg_recyclebin;

   The following result is returned:

   table_id | schema_name |  table_name  |   table_owner    |   dropby  |       drop_time        
   ----------+-------------+--------------+------------------+-----------+------------------------
          16 | public      | tbl2_child_1 | xx_developer   | 1365xxxxx | 2025-04-17 19:33:30+08
   (1 row)

   Recover the child table and check its structure. It is now a standard table and is no longer a child table.

   RECOVER TABLE tbl2_child_1;  
   SELECT hg_dump_script('tbl2_child_1');

   The following result is returned:

    hg_dump_script                        
   --------------------------------------------------------------
    BEGIN;                                                      +
                                                                +
    /*                                                          +
    DROP TABLE public.tbl2_child_1;                             +
    */                                                          +                   
    CREATE TABLE public.tbl2_child_1 (                          +
        id INTEGER                                              +
    ) WITH (                                                    +
    orientation = 'column',                                     +
    storage_format = 'orc',                                     +
    table_group = 'xxxx_tg_default',                            +
    table_storage_mode = 'any',                                 +
    time_to_live_in_seconds = '3153600000'                      +
    );                                                          +
                                                                +
                                                                +
                                                                +
    COMMENT ON TABLE public.tbl2_child_1 IS NULL;               +
    ALTER TABLE public.tbl2_child_1 OWNER TO "xx_developer";    +
                                                                +
                                                                +
    END;                                                        +
    
   (1 row)

3. Delete the parent table. After recovery, it remains a partitioned table.

   -- Delete the parent table.
   DROP TABLE tbl2_parent CASCADE;
   
   -- Check the recycle bin. The parent and child tables are both moved to the recycle bin.
   SELECT * FROM hologres.hg_recyclebin;                   

   The following result is returned:

   table_id | schema_name |  table_name  |   table_owner |      dropby   |       drop_time        
   ---------+-------------+--------------+---------------+---------------+------------------------
         17 | public      | tbl2_child_2 | xx_developer | 1365xxxxxxxx | 2025-04-17 19:41:04+08
         15 | public      | tbl2_parent  | xx_developer | 1365xxxxxxxx | 2025-04-17 19:41:04+08

   Recover the parent table.

   RECOVER TABLE tbl2_parent; 

   Run the following statement in a psql client to view the DDL of the parent table.

   \d+ tbl2_parent;

   The following result is returned, which shows that its child table is also recovered.

                             Partitioned table "public.tbl2_parent"
    Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
   --------+---------+-----------+----------+---------+---------+--------------+-------------
    id     | integer |           |          |         | plain   |              |
   Partition key: LIST (id)
   Partitions: tbl2_child_2 FOR VALUES IN (2)

Example 4: Recover a table with a conflicting name

• If you delete a table and then create a new table with the same name, recovering the deleted table fails. You must rename the existing table first.

  1. Create and delete tbl6.

     CREATE TABLE tbl6(id INT);
     -- Delete the table and move it to the recycle bin.
     DROP TABLE tbl6;

  2. Create a new table that is also named tbl6 and try to recover the deleted table.

     CREATE TABLE tbl6(id INT);
     RECOVER TABLE tbl6;   

     The following result is returned. The recovery fails because a table with the same name already exists. You must delete or rename the existing table.

     ERROR:  Table public.tbl6 already exists

  3. Rename the existing table tbl6 to tbl6_rename and then recover the original tbl6. The recovery now succeeds.

     ALTER TABLE tbl6 RENAME TO tbl6_rename;
     RECOVER TABLE tbl6; 

• If the existing table with the same name has the same primary key and clustering key as the deleted table, renaming it does not work. You must move the existing table to another schema or delete it before recovering the deleted table.

  1. Create a table named tbl1 and delete it.

     -- Create a table and set a PK and a clustering key.
     CREATE TABLE tbl1 (
         col1 INT,
         col2 INT,
         col3 INT,
         PRIMARY KEY (col1, col2)
     )
     WITH (
         clustering_key = 'col1'
     );
     -- Delete the table.
     DROP TABLE  tbl1;
     
     -- Create a new table with the same name, PK, and clustering key.
     CREATE TABLE tbl1 (
         col1 INT,
         col2 INT,
         col3 INT,
         PRIMARY KEY (col1, col2)
     )
     WITH (
         clustering_key = 'col1'
     );
     
     -- Check the recycle bin.
     SELECT * FROM  hologres.hg_recyclebin;

     The following result is returned.

     table_id | schema_name | table_name |   table_owner    |      dropby      |       drop_time        
     ----------+-------------+------------+------------------+------------------+------------------------
            493497 | public      | tbl1         | 13659371xxx| 13659371xxx | 2025-04-17 20:11:08+08

  2. Attempting to recover the tbl1 table from the recycle bin fails.

     -- Attempt to recover the dropped table from the recycle bin. The recovery fails.
     RECOVER  TABLE tbl1;

     The following error message is returned.

     ERROR: Table public.tbl1 already exists

  3. Renaming the tbl1 table and then attempting to recover the original table also fails.

     -- Rename the existing table.
     ALTER TABLE tbl1 RENAME TO tbl2;
     
     -- The recovery still fails because the existing table has a primary key and clustering key.
     RECOVER  TABLE tbl1;

     The following error message is returned.

     ERROR: relation "tbl1_pkey" already EXISTS IN SCHEMA "public" 

  4. Move the new table to another schema. Then, you can successfully recover the original table.

     CREATE SCHEMA test;
     ALTER TABLE tbl2 SET SCHEMA test;
     
     -- The recovery is successful.
     RECOVER  TABLE tbl1;