Re: pgsql: Allow UNIQUE indexes on partitioned tables

I found the following change to be confusing.

/doc/src/sgml/ref/alter_table.sgml
+ <para>
+ Additional restrictions apply when unique indexes are applied to
+ partitioned tables; see <xref linkend="sql-createtable" />.
+ </para>

That paragraph appears in the section covering "ALTER TABLE name ADD
table_constraint_using_index"

However, the code says:

/src/backend/commands/tablecmds.c
+ /*
+ * Doing this on partitioned tables is not a simple feature to
implement,
+ * so let's punt for now.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER TABLE / ADD CONSTRAINT USING INDEX is not
supported on partitioned tables")));

I was expecting the doc for ADD CONSTRAINT USING INDEX to note the
limitation explicitly - in lieu of the above paragraph.

Also, I cannot reason out what the following limitation means:

/doc/src/sgml/ref/create_table.sgml
+ If any partitions are in turn partitioned, all columns of each
partition
+ key are considered at each level below the <literal>UNIQUE</literal>
+ constraint.

As an aside, adding a link to "Data Definiton/Table Partitioning" from at
least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and
"PARTITION OF" in the Parameters section of that page - one must partition
by a table before one can partition it (and also the synopsis lists them in
the BY before OF order), would be helpful.

David J.

On Mon, Feb 19, 2018 at 1:40 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> 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
>
> Branch
> ------
> master
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/eb7ed3f3063401496e4aa4bd68fa33
> f0be31a72f
>
> Modified Files
> --------------
> 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 +-
> src/backend/bootstrap/bootparse.y | 2 +
> src/backend/catalog/index.c | 50 ++++-
> src/backend/catalog/pg_constraint.c | 76 +++++++
> src/backend/catalog/toasting.c | 4 +-
> src/backend/commands/indexcmds.c | 125 +++++++++--
> src/backend/commands/tablecmds.c | 71 ++++++-
> src/backend/parser/analyze.c | 7 +
> src/backend/parser/parse_utilcmd.c | 31 +--
> src/backend/tcop/utility.c | 1 +
> src/bin/pg_dump/t/002_pg_dump.pl | 65 ++++++
> src/include/catalog/index.h | 5 +-
> src/include/catalog/pg_constraint_fn.h | 4 +-
> src/include/commands/defrem.h | 1 +
> src/include/parser/parse_utilcmd.h | 3 +-
> src/test/regress/expected/alter_table.out | 8 -
> src/test/regress/expected/create_index.out | 6 +
> src/test/regress/expected/create_table.out | 12 --
> src/test/regress/expected/indexing.out | 294
> +++++++++++++++++++++++++-
> src/test/regress/expected/insert_conflict.out | 2 +-
> src/test/regress/sql/alter_table.sql | 2 -
> src/test/regress/sql/create_index.sql | 6 +
> src/test/regress/sql/create_table.sql | 8 -
> src/test/regress/sql/indexing.sql | 172 ++++++++++++++-
> 27 files changed, 907 insertions(+), 95 deletions(-)
>
>