PolarDB:Set any column of a partitioned table as the primary key

Prerequisites

The feature is supported on PolarDB for PostgreSQL clusters that run the following engine:

PostgreSQL 14 (revision version 14.10.17.0 or later)

select version();

Parameters

polar_pk_in_non_partition_column_mode: specifies the index type used for the primary key that is not a partition key. Valid values:

• none/local_pk: The primary key cannot be created on a non-partition key. Otherwise, an error is reported.

• global_index: When you create a primary key on a non-partition key, the global index is used as a constraint.

Limits

• If the specified primary key contains all partition keys, the local index is used as the primary key by default. Otherwise, the global index is used as the primary key.

• If the specified constraint contains all partition keys, the local index is used as the unique constraint by default. Otherwise, the global index is used as the unique constraint.

• If you execute the statement to create the primary key for a partitioned table, a global index must be used because a local index may not satisfy the constraint. The same rule applies to the statement for creating the unique constraint.

• No cross-partition update is allowed on a row that is referenced by a foreign key in a partitioned table, because a cross-partition update is essentially a delete operation plus an insert operation. When a delete operation is performed on a row, the system checks whether the row is referenced by a foreign key.

Examples

Set polar_pk_in_non_partition_column_mode to none and create a primary key on a non-partition key.

SET polar_pk_in_non_partition_column_mode = none;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "pt1" lacks column "a" which is part of the partition key.

Set polar_pk_in_non_partition_column_mode to global_index and create a primary key on a non-partition key.

SET polar_pk_in_non_partition_column_mode = global_index;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt1_p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (1);
\d pt1
               Partitioned table "public.pt1"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           |          |
 b      | integer           |           | not null |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt1_pkey" PRIMARY KEY, btree (b) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)

If the primary key of a partitioned table contains all partition keys, the local index is used as the primary key regardless of the polar_pk_in_non_partition_column_mode value. This is the default behavior of PostgreSQL.

CREATE TABLE pt2 (a int primary key, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt2_p1 PARTITION OF pt2 FOR VALUES FROM (0) TO (1);
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a)
Number of partitions: 1 (Use \d+ to list them.)

To use the global index as the primary key, you can execute the statement for modifying the primary key.

ALTER TABLE pt2 DROP CONSTRAINT pt2_pkey;
CREATE UNIQUE INDEX pt2_pkey ON pt2 (a) GLOBAL;
ALTER TABLE pt2 ADD PRIMARY KEY USING INDEX pt2_pkey;
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)