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 15:58:50 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Wed, 2026-02-04 at 17:33 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.1
>
> While I was working on upgrading a PostgreSQL 13 cluster to PostgreSQL 18.1
> I encountered the following problem. pg_upgrade fails if a table has both a
> NOT NULL column and a CHECK constraint with the naming pattern
> {table}_{column}_not_null.
>
> For example consider the following table
>
> -- On PostgreSQL 13
> CREATE TABLE orders (
> id BIGSERIAL,
> customer_id INTEGER NOT NULL,
> CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL)
> );
>
> [...]
>
> pg_restore: error: could not execute query: ERROR: duplicate key value
> violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
> DETAIL: Key (conrelid, contypid, conname)=(16413, 0,
> orders_customer_id_not_null) already exists.
> Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT
> pg_catalog.binary_upgrade_set_next_pg_type_oid('16415'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_type array oid
> SELECT
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16414'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_class oids and relfilenodes
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16413'::pg_catalog.oid);
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_relfilenode('16413'::pg_catalog.oid);
>
> CREATE TABLE "public"."orders" (
> "id" bigint NOT NULL,
> "customer_id" integer NOT NULL,
> CONSTRAINT "orders_customer_id_not_null" CHECK (("customer_id" IS NOT
> NULL))
> );
>
> -- For binary upgrade, set heap's relfrozenxid and relminmxid
> UPDATE pg_catalog.pg_class
> SET relfrozenxid = '502', relminmxid = '1'
> WHERE oid = '"public"."orders"'::pg_catalog.regclass;
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.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch (4.1K, 2-v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch)
download | inline diff:
From 1d4235ca118a44f4b0fae6b8f44c6fd7e6c66205 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
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 <[email protected]>
Author: Laurenz Albe <[email protected]>
Discussion: https://postgr.es/m/[email protected]
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
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