PolarDB:Attach and detach a partition

Background information

• Attach a partition: A common table is added to a partitioned table as a partition of the partitioned table.

• Detach a partition: A partition of the partitioned table is detached and becomes a common table. Instead of deleting some data in the partitioned table directly, this method detaches the data into a normal table and moves it elsewhere.

Syntax

Attach a partition

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

The preceding statement attaches an existing table (which may be partitioned) as a partition of the destination table. You can use the FOR VALUES parameter in the statement to attach the table as a partition for the specified values, or use the DEFAULT parameter to attach the table as a default partition.

In the attachment process, for each index in the destination table, a corresponding index is created in the attached table. If the corresponding index already exists, it is attached to the index of the destination table, in the same way as the ALTER INDEX ATTACH PARTITION statement is executed.

Detach a partition

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

The preceding statement detaches the specified partition from the destination partitioned table. The detached partition becomes a separate table and is independent of the destination table. After the detachment, the destination partitioned table and the independent table have the following changes:

• All indexes attached to the destination table index are detached.

• All triggers cloned from the triggers in the destination table are removed.

• A shared lock is obtained on any table that references the detached partition in a foreign key constraint. For example, partitioned table A references partitioned table B as a foreign key. If partition B_p1 is detached from partitioned table B, a shared lock is applied to partitioned table A.

You can also use CONCURRENTLY or FINALIZE in the preceding statement:

• If you use CONCURRENTLY, a low-level lock is added to avoid blocking other sessions that may be accessing the partitioned table.

  In this mode, two transactions are used internally:

  • During the first transaction, a SHARE UPDATE EXCLUSIVE lock is added to both the parent table and the partition, and the partition is marked as being detached. In this case, this transaction is committed, and other transactions using the parent table wait.

  • After all waiting transactions are complete, the second transaction obtains the SHARE UPDATE EXCLUSIVE lock on the parent table and the ACCESS EXCLUSIVE lock on the partition, and the detachment process ends. 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

Attach a partition

Attach the cities_partdef partition to the cities partitioned table as the default partition.

ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

Detach a partition

Detach the measurement_y2015m12 partition from the measurement partitioned table as an independent table.

ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;