PolarDB:Attach tables to or detach partitions from a partitioned table

Overview

• Attachment: Attach an existing table to a partitioned table as a partition of that partitioned table.

• Detachment: Detach a partition from a partitioned table to turn the partition into an independent table. You can use the detachment operation when you want to remove data from a partitioned table without deleting the data.

Syntax

Attachment

ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }

In the statement, an existing table (which can be a partitioned table) is attached to a partitioned table and becomes a partition of that table. You can use the FOR VALUES clause to specify the destination partition or use DEFAULT to make it a default partition.

In this process, for each partition in the target table, a corresponding index is created for the attached table. If the index already exists, it will be attached to the index of the destination table, as if an ALTER INDEX ATTACH PARTITION command is executed.

Detachment

ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

The statement detaches the specified partition from the table. The detached partition becomes an independent table and is no longer associated with its previous parent table. The operation also leads to the following changes:

• All indexes associated with the partition will also be detached.

• All triggers cloned from the triggers of the parent table are deleted.

• All tables that reference the partition through a foreign key will obtain a shared lock. For example, assume that the partitioned table A references partition B through a foreign key. If a partition B_p1 is detached from the partitioned table B, the partitioned table A will obtain a shared lock.

In the statement, CONCURRENTLY or FINALIZE can also be specified:

• If CONCURRENTLY is specified, the detachment operation is performed on a low-level lock to avoid blocking other transactions on the partitioned table.

  Two transactions are created.

  • In the first transaction, a SHARE UPDATE EXCLUSIVE lock takes effect on the parent table and the partition is tagged as being attached. This transaction has been committed, and all other transactions using the partitioned table are paused.

  • After the transaction is completed, the second transaction obtains the SHARE UPDATE EXCLUSIVE lock on the partitioned table and the ACCESS EXCLUSIVE lock on the partition. The detachment process is completed. The check constraint that is identical with a partition constraint is added to the partition.

  Note

  CONCURRENTLY cannot be used in a transaction block. In addition, if the partitioned table contains a default partition, CONCURRENTLY cannot be used.

• If FINALIZE is used, a DETACH CONCURRENTLY call that was previously canceled or interrupted will be complete.

Examples

Attachment

A table named cities_partdef is attached to a partitioned table named cities as the default partition.

ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

Detachment

A partition named measurement_y2015m12 is detached from a partitioned table named measurement and becomes an independent table.

ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;