From eb7ed3f3063401496e4aa4bd68fa33f0be31a72f Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 19 Feb 2018 16:59:37 -0300 Subject: Allow UNIQUE indexes on partitioned tables If we restrict unique constraints on partitioned tables so that they must always include the partition key, then our standard approach to unique indexes already works --- each unique key is forced to exist within a single partition, so enforcing the unique restriction in each index individually is enough to have it enforced globally. Therefore we can implement unique indexes on partitions by simply removing a few restrictions (and adding others.) Discussion: https://postgr.es/m/20171222212921.hi6hg6pem2w2t36z@alvherre.pgsql Discussion: https://postgr.es/m/20171229230607.3iib6b62fn3uaf47@alvherre.pgsql Reviewed-by: Simon Riggs, Jesper Pedersen, Peter Eisentraut, Jaime Casanova, Amit Langote --- doc/src/sgml/ddl.sgml | 9 ++++----- doc/src/sgml/ref/alter_table.sgml | 15 +++++++++++---- doc/src/sgml/ref/create_index.sgml | 5 +++++ doc/src/sgml/ref/create_table.sgml | 18 ++++++++++++++++-- 4 files changed, 36 insertions(+), 11 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8c3be5b103c..15a9285136d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3146,9 +3146,8 @@ CREATE TABLE measurement_y2006m02 PARTITION OF measurement Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. (The key index is not strictly - necessary, but in most scenarios it is helpful. If you intend the key - values to be unique then you should always create a unique or - primary-key constraint for each partition.) This automatically creates + necessary, but in most scenarios it is helpful.) + This automatically creates one index on each partition, and any partitions you create or attach later will also contain the index. @@ -3270,7 +3269,7 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 - There is no way to create a primary key, unique constraint, or + There is no way to create a exclusion constraint spanning all partitions; it is only possible to constrain each leaf partition individually. @@ -3278,7 +3277,7 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 - Since primary keys are not supported on partitioned tables, foreign + While primary keys are supported on partitioned tables, foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to some other table. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 2b514b76064..5be56d4b287 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -412,6 +412,11 @@ WITH ( MODULUS numeric_literal, REM disappear too. + + Additional restrictions apply when unique or primary key constraints + are added to partitioned tables; see . + + Adding a constraint using an existing index can be helpful in @@ -834,9 +839,9 @@ WITH ( MODULUS numeric_literal, REM This form attaches an existing table (which might itself be partitioned) as a partition of the target table. The table can be attached - as a partition for specific values using FOR VALUES - or as a default partition by using DEFAULT - . For each index in the target table, a corresponding + as a partition for specific values using FOR VALUES + or as a default partition by using DEFAULT. + For each index in the target table, a corresponding one will be created in the attached table; or, if an equivalent index already exists, will be attached to the target table's index, as if ALTER INDEX ATTACH PARTITION had been executed. @@ -851,8 +856,10 @@ WITH ( MODULUS numeric_literal, REM as the target table and no more; moreover, the column types must also match. Also, it must have all the NOT NULL and CHECK constraints of the target table. Currently - UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not considered. + UNIQUE and PRIMARY KEY constraints + from the parent table will be created in the partition, if they don't + already exist. If any of the CHECK constraints of the table being attached is marked NO INHERIT, the command will fail; such a constraint must be recreated without the NO INHERIT diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index f464557de81..1fd21e12bd7 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -108,6 +108,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + + + Additional restrictions apply when unique indexes are applied to + partitioned tables; see . + diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 8bf9dc992be..338dddd7ccd 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -546,8 +546,7 @@ WITH ( MODULUS numeric_literal, REM - Partitioned tables do not support UNIQUE, - PRIMARY KEY, EXCLUDE, or + Partitioned tables do not support EXCLUDE or FOREIGN KEY constraints; however, you can define these constraints on individual partitions. @@ -786,6 +785,14 @@ WITH ( MODULUS numeric_literal, REM primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.) + + + 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. + @@ -814,6 +821,13 @@ WITH ( MODULUS numeric_literal, REM about the design of the schema, since a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. + + + PRIMARY KEY constraints share the restrictions that + UNIQUE constraints have when placed on partitioned + tables. + + -- cgit v1.2.3