Hologres:REBUILD

Command format

ASYNC REBUILD TABLE [ IF EXISTS ] <table_name>
    [ WITH ( <rebuild_parameter> [= <value>] [, ... ] )]  
    <action> [, ... ];
    
WHERE action IS ONE OF:
    ADD [ COLUMN ] <column_name> <data_type> [ column_constraint [ ... ] ]
    ALTER [ COLUMN ] <column_name> [ SET DATA ] TYPE <data_type> [ USING <expression> ]
    ALTER [ COLUMN ] <column_name> SET DEFAULT <expression>
    ALTER [ COLUMN ] <column_name> DROP DEFAULT
    ALTER [ COLUMN ] <column_name> { SET | DROP } NOT NULL
    ALTER PRIMARY KEY (<column_name> [, ...])
    TO [LOGICAL] PARTITION [BY LIST(<column_name> [, <column_name>])]
    SET ( <parameter> [= <value>] [, ... ] )

WHERE rebuild_parameter IS ONE OF:
    keep_source
    binlog_mode
    rebuild_guc_<guc_name> = '<guc_value>'

Parameter description

Run REBUILD on a table without binary logging enabled

-- Create a table and import data
CREATE TABLE rebuild_test (
    a TEXT,
    b TEXT,
    ds TEXT
);

INSERT INTO rebuild_test VALUES ('1', '1', '2025-04-01'), ('2', '2', '2025-04-02'), ('3', '3', '2025-04-03');

-- Add a NOT NULL column and set a default value
ASYNC REBUILD TABLE rebuild_test ADD COLUMN c text NOT NULL DEFAULT 'a';

-- Change the primary key to column a
ASYNC REBUILD TABLE rebuild_test ALTER PRIMARY KEY (a);

-- Use Serverless resources to run the Rebuild task, set the distribution_key and clustering_key for the table, and change the storage format to row-column hybrid
ASYNC REBUILD TABLE rebuild_test 
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    distribution_key = 'a',
    clustering_key = 'a',
    orientation = 'row,column'
);

-- Convert the non-partitioned table to a logical partitioned table, set the partition key to ds, and add the NOT NULL property to the ds column
ASYNC REBUILD TABLE rebuild_test 
    ALTER COLUMN ds SET NOT NULL,
    TO LOGICAL PARTITION BY LIST(ds);

Run REBUILD on a table with binary logging enabled

REBUILD does not retain historical binary logging data. By default, you cannot run REBUILD on a table that has binary logging enabled. To run the task, you must specify the binlog_mode parameter and follow these steps. This ensures that the downstream client has fully consumed all historical binary logging data.

1. Run REBUILD.

   ASYNC REBUILD TABLE rebuild_test 
   WITH (
     binlog_mode
   )
   <YOUR_ACTION>;

2. For a REBUILD task with the binlog_mode parameter set, the task automatically pauses after the set_readonly step is complete. You can query the progress using the following SQL statement. At this point, the system has set the table to read-only. Data cannot be written to the table, which means no new binary logging data is generated.

   postgres=# SELECT step, status, progress FROM hologres.rebuild_progress('<query_id>');
                step              | status | progress 
   -------------------------------+--------+----------
    prepare                       | done   | 1/1
    create_tmp_table              | done   | 1/1
    get_src_table_snapshot        | done   | 1/1
    insert                        | done   | 1/1
    set_readonly                  | done   | 1/1
    check_snapshot                |        | 0/1
    re-insert                     |        | -
    check_additional_child_table  |        | -
    create_additional_child_table |        | -
    insert_additional_child_table |        | -
    swap                          |        | 0/1
   (11 rows)

3. Wait for the downstream client to finish consuming the existing binary logging data. Then, manually run the following SQL statement to resume the REBUILD task. While the task resumes, the source table remains read-only, and no new binary logging data is generated.

   RESUME '<query_id>';

   After the REBUILD task is successfully run, binary logging is automatically enabled for the new table. You can then restart the downstream binary logging consumption task and start consuming the binary logs of the new table from lsn = 0.

View the execution status of a REBUILD task

REBUILD tasks run asynchronously. After a task is submitted, the system returns a success status and a query_id. You must query the hologres.rebuild_progress system table to view the status of the asynchronous subtasks. The table is considered rebuilt only after all subtasks are successful. The command is as follows:

SELECT * FROM hologres.rebuild_progress('<rebuild_query_id>');

The columns of the system table and their descriptions are as follows:

The following is an example of the result:

Stop and restart a REBUILD task

• Stop an asynchronous REBUILD task.

  SUSPEND '<query_id>';

• Resumes an asynchronous task that was canceled by CANCEL.

  RESUME '<query_id>';

Handle REBUILD task exceptions

If a REBUILD task is interrupted by an error, or if you manually run the SUSPEND command to pause the task, you can run the RESUME command to continue the task. You can also follow these steps to end the task and recover the source table.

• Run the following command to clean up the temporary tables generated during the REBUILD process.

  CALL hg_clean_rebuild_tmp_tables('<query_id>');

• If the source table was set to read-only by the REBUILD task before the task was interrupted, run the following command to remove the read-only state and resume write operations.

  ALTER TABLE <table_name> SET (readonly = false);