v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch

From 1d4235ca118a44f4b0fae6b8f44c6fd7e6c66205 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Thu, 5 Feb 2026 15:52:10 +0100
Subject: [PATCH v1] Avoid name collision with NOT NULL constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

If a CREATE TABLE statement defined a constraint whose name
is identical to the name generated for a NOT NULL constraint,
PostgreSQL would throw a unique key violation error on
"pg_constraint_conrelid_contypid_conname_index".

To fix, pass the constraints created by AddRelationNewConstraints()
to AddRelationNotNullConstraints(), so that the latter can avoid
name collisions with the constraint names it generates.

Bug: #19393
Reported-by: Hüseyin Demir <huseyin.d3r@gmail.com>
Author: Laurenz Albe <laurenz.albe@cybertec.at>
Discussion: https://postgr.es/m/19393-6a82427485a744cf@postgresql.org
Backpatch-through: 18
---
 src/backend/catalog/heap.c       | 12 ++++++++++--
 src/backend/commands/tablecmds.c |  7 ++++---
 src/include/catalog/heap.h       |  3 ++-
 3 files changed, 16 insertions(+), 6 deletions(-)

diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 606434823cf..35cc6b506e0 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2885,14 +2885,16 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
  * for each column, giving priority to user-specified ones, and setting
  * inhcount according to how many parents cause each column to get a
  * not-null constraint.  If a user-specified name clashes with another
- * user-specified name, an error is raised.
+ * user-specified name, an error is raised.  'existing_constraints'
+ * is a list of already defined constraints, whose names should be avoided
+ * when generating constraint names.
  *
  * Returns a list of AttrNumber for columns that need to have the attnotnull
  * flag set.
  */
 List *
 AddRelationNotNullConstraints(Relation rel, List *constraints,
-							  List *old_notnulls)
+							  List *old_notnulls, List *existing_constraints)
 {
 	List	   *givennames;
 	List	   *nnnames;
@@ -2905,6 +2907,12 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 	 * system-generated name conflicts we just generate another.
 	 */
 	nnnames = NIL;
+	foreach_ptr(CookedConstraint, cons, existing_constraints)
+	{
+		if (cons->name != NULL)
+			nnnames = lappend(nnnames, cons->name);
+	}
+
 	givennames = NIL;
 
 	/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..ca68ee8c990 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -781,6 +781,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	List	   *rawDefaults;
 	List	   *cookedDefaults;
 	List	   *nncols;
+	List	   *conlist = NIL;
 	Datum		reloptions;
 	ListCell   *listptr;
 	AttrNumber	attnum;
@@ -1338,8 +1339,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	 * up.
 	 */
 	if (stmt->constraints)
-		AddRelationNewConstraints(rel, NIL, stmt->constraints,
-								  true, true, false, queryString);
+		conlist = AddRelationNewConstraints(rel, NIL, stmt->constraints,
+											true, true, false, queryString);
 
 	/*
 	 * Finally, merge the not-null constraints that are declared directly with
@@ -1348,7 +1349,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	 * columns that don't yet have it.
 	 */
 	nncols = AddRelationNotNullConstraints(rel, stmt->nnconstraints,
-										   old_notnulls);
+										   old_notnulls, conlist);
 	foreach_int(attrnum, nncols)
 		set_attnotnull(NULL, rel, attrnum, true, false);
 
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 624c415dadb..6c9ac812aa0 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -117,7 +117,8 @@ extern List *AddRelationNewConstraints(Relation rel,
 									   const char *queryString);
 extern List *AddRelationNotNullConstraints(Relation rel,
 										   List *constraints,
-										   List *old_notnulls);
+										   List *old_notnulls,
+										   List *existing_constraints);
 
 extern void RelationClearMissing(Relation rel);
 
-- 
2.53.0