Allow UNIQUE indexes on partitioned tables

Enterprise / PostgreSQL - Alvaro Herrera [alvh.no-ip.org] - 19 February 2018 20:40 EST

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

eb7ed3f306 Allow UNIQUE indexes on partitioned tables
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(-)

Upstream: git.postgresql.org


  • Share