From 1d4235ca118a44f4b0fae6b8f44c6fd7e6c66205 Mon Sep 17 00:00:00 2001 From: Laurenz Albe 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 Author: Laurenz Albe 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