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

4 files changed, 36 insertions, 11 deletions

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
       <para>
        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
     <itemizedlist>
      <listitem>
       <para>
-       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.
       </para>
@@ -3278,7 +3277,7 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
 
      <listitem>
       <para>
-       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.
       </para>
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 <replaceable class="parameter">numeric_literal</replaceable>, REM
       disappear too.
      </para>
 
+     <para>
+      Additional restrictions apply when unique or primary key constraints
+      are added to partitioned tables; see <xref linkend="sql-createtable" />.
+     </para>
+
      <note>
       <para>
        Adding a constraint using an existing index can be helpful in
@@ -834,9 +839,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       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 <literal>FOR VALUES
-      </literal> or as a default partition by using <literal>DEFAULT
-      </literal>.  For each index in the target table, a corresponding
+      as a partition for specific values using <literal>FOR VALUES</literal>
+      or as a default partition by using <literal>DEFAULT</literal>.
+      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 <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
@@ -851,8 +856,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       as the target table and no more; moreover, the column types must also
       match.  Also, it must have all the <literal>NOT NULL</literal> and
       <literal>CHECK</literal> constraints of the target table.  Currently
-      <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
       <literal>FOREIGN KEY</literal> constraints are not considered.
+      <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints
+      from the parent table will be created in the partition, if they don't
+      already exist.
       If any of the <literal>CHECK</literal> constraints of the table being
       attached is marked <literal>NO INHERIT</literal>, the command will fail;
       such a constraint must be recreated without the <literal>NO INHERIT</literal>
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 ] <replaceable class=
         insert or update data which would result in duplicate entries
         will generate an error.
        </para>
+
+       <para>
+        Additional restrictions apply when unique indexes are applied to
+        partitioned tables; see <xref linkend="sql-createtable" />.
+       </para>
       </listitem>
      </varlistentry>
 
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 <replaceable class="parameter">numeric_literal</replaceable>, REM
      </para>
 
      <para>
-      Partitioned tables do not support <literal>UNIQUE</literal>,
-      <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or
+      Partitioned tables do not support <literal>EXCLUDE</literal> or
       <literal>FOREIGN KEY</literal> constraints; however, you can define
       these constraints on individual partitions.
      </para>
@@ -786,6 +785,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       primary key constraint defined for the table.  (Otherwise it
       would just be the same constraint listed twice.)
      </para>
+
+     <para>
+      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 <literal>UNIQUE</literal>
+      constraint.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -814,6 +821,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, 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.
      </para>
+
+     <para>
+      <literal>PRIMARY KEY</literal> constraints share the restrictions that
+      <literal>UNIQUE</literal> constraints have when placed on partitioned
+      tables.
+     </para>
+
     </listitem>
    </varlistentry>