Perform DDL operations on partitioned tables with global indexes - PolarDB

When a partitioned table has global indexes, DDL operations on that table affect index state across all partitions. Use the UPDATE GLOBAL INDEXES or INVALIDATE GLOBAL INDEXES clause to control this behavior explicitly, or set the polar_enable_invalidate_global_index parameter to change the default behavior.

Syntax

ALTER TABLE relation_expr alter_partition_cmd [update_global_index_clause]

alter_partition_cmd = {
  DROP PARTITION partition_name |
  DROP PARTITION FOR partition_values |
  ADD PARTITION partition_name table_partition_description |
  ...
}

update_global_index_clause = { UPDATE | INVALIDATE } GLOBAL INDEXES

Clause behavior

INVALIDATE GLOBAL INDEXES

All global indexes on the partitioned table are invalidated when the DDL operation completes.

INVALIDATE GLOBAL INDEXES has no effect for ADD PARTITION and MOVE PARTITION. Global indexes remain in their current state after these operations even if you include this clause.

UPDATE GLOBAL INDEXES

All global indexes on the partitioned table are updated when the DDL operation completes.

The current version does not support UPDATE GLOBAL INDEXES. Using this clause causes an error.

Default behavior (no clause specified)

By default, running a DDL statement on a partitioned table that has global indexes causes an error.

To change this default, set the polar_enable_invalidate_global_index parameter to on. With this parameter enabled, all global indexes on the partitioned table are invalidated automatically — without requiring the INVALIDATE GLOBAL INDEXES clause. This automatic invalidation also applies when the DDL statement includes TRUNCATE, CLUSTER, or VACUUM FULL.

Supported operations

The following table shows which DDL operations are supported on partitioned tables that have global indexes, and how each clause affects index state.

Operation	`INVALIDATE GLOBAL INDEXES`	`UPDATE GLOBAL INDEXES`
Drop partition	Invalidates all global indexes	Not supported — causes an error
Split partition	Invalidates all global indexes	Not supported — causes an error
Merge partition	Invalidates all global indexes	Not supported — causes an error
Exchange partition	Invalidates all global indexes	Not supported — causes an error
Truncate partition	Invalidates all global indexes	Not supported — causes an error
Truncate child table	Invalidates all global indexes	Not supported — causes an error
`CLUSTER` / `VACUUM FULL` on child table	Invalidates all global indexes	Not supported — causes an error
Add partition	No effect — indexes remain in current state	Not supported — causes an error
Move partition	No effect — indexes remain in current state	Not supported — causes an error

Examples

Add a partition

The following examples show the three clause variants for ADD PARTITION and the outcome of each.

-- No clause: causes an error if global indexes exist (default behavior).
ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400);

-- UPDATE GLOBAL INDEXES: not supported in the current version; causes an error.
ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400) UPDATE GLOBAL INDEXES;

-- INVALIDATE GLOBAL INDEXES: clause has no effect for ADD PARTITION;
-- global indexes remain in their current state.
ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400) INVALIDATE GLOBAL INDEXES;

Drop a partition

-- UPDATE GLOBAL INDEXES: not supported in the current version; causes an error.
ALTER TABLE gi_ora DROP PARTITION p4 UPDATE GLOBAL INDEXES;

-- INVALIDATE GLOBAL INDEXES: all global indexes are invalidated.
ALTER TABLE gi_ora DROP PARTITION p4 INVALIDATE GLOBAL INDEXES;