Hologres:Table recycle bin

Example 1: Delete and recover a standard 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. View the recycle bin to confirm that the table is present.

   SELECT * FROM hologres.hg_recyclebin;

   The result is as follows:

   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)

   View the storage of the table:

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

   The storage result is as follows:

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

3. Recover the table.

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

Example 2: Delete and recover a partitioned table

1. Create a partitioned table with a parent table 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 and recover one of the child tables. The recovered child table becomes a standard table. You must manually attach it to the original partitioned parent table.

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

   The result is as follows:

   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 partitioned child table and check its structure. The table is now a standard table, not a partitioned child table.

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

   The result is as follows:

    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 partitioned parent table. After recovery, it is restored as a partitioned table.

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

   The following is the result:

   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

   You can recover the partitioned parent table.

   RECOVER TABLE tbl2_parent; 

   You can run the following statement in a PSQL client to view the DDL of the partitioned parent table.

   \d+ tbl2_parent;

   The 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, the recovery of the deleted table fails. You must first rename the existing table.

  1. Create and delete tbl6.

     CREATE TABLE tbl6(id INT);
     -- Delete the table. The table is moved 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 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 new table with the same name also has the same PK and clustering key as the deleted table, renaming it does not resolve the conflict. You must move the new table to a different schema or delete it to recover the original table.

  1. Create the table tbl1 and then execute the deletion.

     -- 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;
     
     -- Recreate a table with the same name, 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'
     );
     
     -- View the recycle bin.
     SELECT * FROM  hologres.hg_recyclebin;

     The result is as follows.

     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 directly from the recycle bin fails.

     -- Recover the deleted table from the recycle bin. An error is reported and the recovery fails.
     RECOVER  TABLE tbl1;

     The error is as follows.

     ERROR: Table public.tbl1 already exists

  3. Renaming the tbl1 table and then trying to recover it also fails.

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

     The following error message is returned.

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

  4. Move the tbl1 table to another schema and then recover it. The recovery then succeeds.

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