v1-0001-Disallow-NULLS-NOT-DISTINCT-indexes-for-primary-k.patch

From 838b82bb33a14b499e2e6c26d661363100f2a76f Mon Sep 17 00:00:00 2001
From: Daniel Gustafsson <dgustafsson@postgresql.org>
Date: Wed, 14 Dec 2022 23:23:58 +0100
Subject: [PATCH v1] 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 <zedaardv@drizzle.com>
Discussion: https://postgr.es/m/17720-dab8ee0fa85d316d@postgresql.org
---
 src/backend/catalog/index.c                | 7 +++++++
 src/bin/pg_dump/pg_dump.c                  | 2 +-
 src/test/regress/expected/create_index.out | 6 ++++++
 src/test/regress/sql/create_index.sql      | 6 ++++++
 4 files changed, 20 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 61f1d3926a..6b889c582f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -225,6 +225,13 @@ index_check_primary_key(Relation heapRel,
 						RelationGetRelationName(heapRel))));
 	}
 
+	if ((is_alter_table && 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 44d957c038..2d767cb286 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16416,7 +16416,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
 		{
 			appendPQExpBufferStr(q,
 								 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
-			if (indxinfo->indnullsnotdistinct)
+			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.32.1 (Apple Git-133)