From d9595232579a3a9fadf4ce0b4cd58c1a3fc3b2f7 Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson Date: Fri, 24 Feb 2023 11:09:50 +0100 Subject: [PATCH] Disallow NULLS NOT DISTINCT indexes for primary keys A unique index which is created with non-distinct NULLS cannot be used for backing a primary key constraint. Make sure to disallow such table alterations and teach pg_dump to drop the non-distinct NULLS clause on indexes where this has been set. Bug: 17720 Reported-by: Reiner Peterke Reviewed-by: Peter Eisentraut Reviewed-by: Tom Lane Discussion: https://postgr.es/m/17720-dab8ee0fa85d316d@postgresql.org --- src/backend/catalog/index.c | 13 +++++++++++++ src/bin/pg_dump/pg_dump.c | 7 ++++++- src/test/regress/expected/create_index.out | 6 ++++++ src/test/regress/sql/create_index.sql | 6 ++++++ 4 files changed, 31 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 41b16cb89b..7777e7ec77 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -225,6 +225,19 @@ index_check_primary_key(Relation heapRel, RelationGetRelationName(heapRel)))); } + /* + * Indexes created with NULLS NOT DISTINCT cannot be used for primary key + * constraints. While there is no direct syntax to reach here, it can be + * done by creating a separate index and attaching it via ALTER TABLE .. + * USING INDEX. + */ + if (indexInfo->ii_NullsNotDistinct) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("primary keys cannot use NULLS NOT DISTINCT indexes"))); + } + /* * Check that all of the attributes in a primary key are marked as not * null. (We don't really expect to see that; it'd mean the parser messed diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index cc424fd3b2..24ba936332 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -16431,7 +16431,12 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) { appendPQExpBufferStr(q, coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE"); - if (indxinfo->indnullsnotdistinct) + /* + * PRIMARY KEY constraints should not be using NULLS NOT DISTINCT + * indexes. Being able to create this was fixed, but we need to + * make the index distinct in order to be able to restore the dump. + */ + if (indxinfo->indnullsnotdistinct && coninfo->contype != 'p') appendPQExpBufferStr(q, " NULLS NOT DISTINCT"); appendPQExpBufferStr(q, " ("); for (k = 0; k < indxinfo->indnkeyattrs; k++) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 6cd57e3eaa..acfd9d1f4f 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1595,6 +1595,12 @@ create unique index on cwi_test (a); alter table cwi_test add primary key using index cwi_test_a_idx ; ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables DROP TABLE cwi_test; +-- PRIMARY KEY constraint cannot be backed by a NULLS NOT DISTINCT index +CREATE TABLE cwi_test(a int, b int); +CREATE UNIQUE INDEX cwi_a_nnd ON cwi_test (a) NULLS NOT DISTINCT; +ALTER TABLE cwi_test ADD PRIMARY KEY USING INDEX cwi_a_nnd; +ERROR: primary keys cannot use NULLS NOT DISTINCT indexes +DROP TABLE cwi_test; -- -- Check handling of indexes on system columns -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index a3738833b2..d49ce9f300 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -617,6 +617,12 @@ create unique index on cwi_test (a); alter table cwi_test add primary key using index cwi_test_a_idx ; DROP TABLE cwi_test; +-- PRIMARY KEY constraint cannot be backed by a NULLS NOT DISTINCT index +CREATE TABLE cwi_test(a int, b int); +CREATE UNIQUE INDEX cwi_a_nnd ON cwi_test (a) NULLS NOT DISTINCT; +ALTER TABLE cwi_test ADD PRIMARY KEY USING INDEX cwi_a_nnd; +DROP TABLE cwi_test; + -- -- Check handling of indexes on system columns -- -- 2.39.5