public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
Date: Thu, 05 Feb 2026 17:52:50 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Thu, 2026-02-05 at 15:58 +0100, I wrote:
> The bug is actually not in pg_upgrade, but in CREATE TABLE. The attached patch
> fixes the problem for me by avoiding given constraint names when generating
> the names for NOT NULL constraints.
... and here is v2, including a regression test.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v2-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch (5.5K, 2-v2-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch)
download | inline diff:
From 7b7b165a574f12ebe77005def9af3cf2782209a0 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Thu, 5 Feb 2026 17:47:38 +0100
Subject: [PATCH v2] 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 <[email protected]>
Author: Laurenz Albe <[email protected]>
---
src/backend/catalog/heap.c | 12 ++++++++++--
src/backend/commands/tablecmds.c | 7 ++++---
src/include/catalog/heap.h | 3 ++-
src/test/regress/expected/create_table.out | 6 ++++++
src/test/regress/sql/create_table.sql | 7 +++++++
5 files changed, 29 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);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 029beb20aae..90639ea0d89 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -161,6 +161,12 @@ ALTER TABLE remember_node_subid ALTER c TYPE bigint;
SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
COMMIT;
DROP TABLE remember_node_subid;
+-- generated NOT NULL constraint names must not collide with explicitly named constraints
+CREATE TABLE two_not_null_constraints (
+ col integer NOT NULL,
+ CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
+);
+DROP TABLE two_not_null_constraints;
--
-- Partitioned tables
--
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 9b3e93b4164..80e424e6bda 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -105,6 +105,13 @@ SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
COMMIT;
DROP TABLE remember_node_subid;
+-- generated NOT NULL constraint names must not collide with explicitly named constraints
+CREATE TABLE two_not_null_constraints (
+ col integer NOT NULL,
+ CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
+);
+DROP TABLE two_not_null_constraints;
+
--
-- Partitioned tables
--
--
2.53.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox