From 63cbee6a786028790abc6ae9b9606366bae5e01d Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 12 Mar 2018 13:28:52 -0300 Subject: [PATCH] doc: Reword restriction on partition keys in unique indexes New wording from David G. Johnston, who noticed the unreadable original also. Include his suggested test case as well. Fix a typo I noticed elsewhere while doing this. Discussion: https://postgr.es/m/CAKFQuwY4Ld7ecxL_KAmaxwt0FUu5VcPPN2L4dh+3BeYbrdBa5g@mail.gmail.com --- doc/src/sgml/ref/create_table.sgml | 9 ++++----- src/test/regress/expected/alter_table.out | 2 +- src/test/regress/expected/indexing.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 2 +- src/test/regress/sql/indexing.sql | 7 +++++++ 5 files changed, 21 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 9e8e9d8f1c..14a43b45e9 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -791,11 +791,10 @@ WITH ( MODULUS numeric_literal, REM - When used on partitioned tables, unique constraints must include all the - columns of the partition key. - If any partitions are in turn partitioned, all columns of each partition - key are considered at each level below the UNIQUE - constraint. + When establishing a unique constraint for a multi-level partition + hierarchy, all the columns in the partition key of the target + partitioned table, as well as those of all its descendant partitioned + tables, must be included in the constraint definition. diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index ccd2c38dbc..04838dfad1 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3646,7 +3646,7 @@ ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); ERROR: circular inheritance not allowed DETAIL: "list_parted2" is already a child of "list_parted2". -- If a partitioned table being created or an existing table being attached --- as a paritition does not have a constraint that would allow validation scan +-- as a partition does not have a constraint that would allow validation scan -- to be skipped, but an individual partition does, then the partition's -- validation scan is skipped. CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 375f55b337..3fa47cba96 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -885,6 +885,14 @@ for values from (0) to (1000) partition by range (b); -- fail ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "idxpart2" lacks column "b" which is part of the partition key. drop table idxpart; +-- Ditto for the ATTACH PARTITION case +create table idxpart (a int primary key, b int) partition by range (a); +create table idxpart1 (a int not null, b int, primary key (a, b)) + partition by range (a, b); +alter table idxpart attach partition idxpart1 for values from (1) to (1000); +ERROR: insufficient columns in PRIMARY KEY constraint definition +DETAIL: PRIMARY KEY constraint on table "idxpart1" lacks column "b" which is part of the partition key. +DROP TABLE idxpart, idxpart1; -- Multi-layer partitioning works correctly in this case: create table idxpart (a int, b int, primary key (a, b)) partition by range (a); create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b); diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index b73f523e8a..66f5e50d97 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2348,7 +2348,7 @@ ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); -- If a partitioned table being created or an existing table being attached --- as a paritition does not have a constraint that would allow validation scan +-- as a partition does not have a constraint that would allow validation scan -- to be skipped, but an individual partition does, then the partition's -- validation scan is skipped. CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index f3d0387f34..902d8c59ca 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -450,6 +450,13 @@ create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b); -- fail drop table idxpart; +-- Ditto for the ATTACH PARTITION case +create table idxpart (a int primary key, b int) partition by range (a); +create table idxpart1 (a int not null, b int, primary key (a, b)) + partition by range (a, b); +alter table idxpart attach partition idxpart1 for values from (1) to (1000); +DROP TABLE idxpart, idxpart1; + -- Multi-layer partitioning works correctly in this case: create table idxpart (a int, b int, primary key (a, b)) partition by range (a); create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b); -- 2.40.0