BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19393
Logged by:          Hüseyin Demir
Email address:      huseyin.d3r@gmail.com
PostgreSQL version: 18.1
Operating system:   CentOS 9
Description:

Hello,

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

```sql
-- 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)
);
```
Root cause

Starting in PostgreSQL 17, NOT NULL constraints are stored in pg_constraint
(with contype = 'n') rather than only in pg_attribute.attnotnull. During
pg_upgrade, PostgreSQL auto-generates constraint names following the pattern
{table}_{column}_not_null. If a CHECK constraint with this name already
exists, the upgrade fails with a duplicate key violation.

I had to find all the constraints and rename them to mitigate the error but
I want to know if it's the only solution or is this a problem that can be
addressed inside pg_upgrade binary.

To reproduce a the issue

- Create the following table on PostgreSQL 13

```sql

-- 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)
);
```

- Confirm the not null check constraint.

```sql
SELECT
    c.conname,
    c.conrelid::regclass AS table_name,
    c.contype,
    pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.conrelid::regclass::text LIKE 'orders%'
ORDER BY c.conrelid::regclass, c.conname;
           conname           | table_name | contype |            definition
-----------------------------+------------+---------+-----------------------------------
 orders_customer_id_not_null | orders     | c       | CHECK ((customer_id IS
NOT NULL))
(1 row)
```

- Execute pg_upgrade with the following options.

```bash
[postgres@2001-1c00-5d82-ce00-c88f-20ff-fec5-d892 ~]$
/usr/pgsql-18/bin/pg_upgrade --link -b /usr/pgsql-13/bin/ -B
/usr/pgsql-18/bin/ -d /var/lib/postgresql/13/data -D
/var/lib/postgresql/18/data -r -j 5
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for user-defined encoding conversions                ok
Checking for user-defined postfix operators                   ok
Checking for incompatible polymorphic functions               ok
Checking for not-null constraint inconsistencies              ok
Creating dump of global objects                               ok
Creating dump of database schemas
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting the default char signedness for new cluster           ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
  benchmark_v1
*failure*

Consult the last few lines of
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
for
the probable cause of the failure.
Failure, exiting

*failure*

Consult the last few lines of
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_14448.log"
for
the probable cause of the failure.
Failure, exiting

child process exited abnormally: status 256
Failure, exiting
[postgres@2001-1c00-5d82-ce00-c88f-20ff-fec5-d892 ~]$

command: "/usr/pgsql-18/bin/pg_dump" --host /var/lib/pgsql --port 50432
--username postgres --no-data --sequence-data  --quote-all-identifiers
--binary-upgrade --format=custom --statistics --no-sync
--file="/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/dump/pg_upgrade_dump_16384.custom"
'dbname=benchmark_v1' >>
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
2>&1


command: "/usr/pgsql-18/bin/pg_restore" --host /var/lib/pgsql --port 50432
--username postgres --create --exit-on-error --verbose
--transaction-size=200 --dbname template1
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/dump/pg_upgrade_dump_16384.custom"
>>
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
2>&1
pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE "benchmark_v1"
pg_restore: connecting to new database "benchmark_v1"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE PROPERTIES "benchmark_v1"
pg_restore: connecting to new database "benchmark_v1"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating TABLE "public.orders"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 209; 1259 16413 TABLE orders postgres
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;
```
Expected Behavior

pg_upgrade --check should detect this naming collision before the upgrade
and report a warning, or pg_upgrade should generate an alternative
constraint name when a collision exists.

Workaround

Before upgrade, rename conflicting CHECK constraints:

```sql
ALTER TABLE orders
    RENAME CONSTRAINT orders_customer_id_not_null
    TO orders_customer_id_check;
```

After upgrade, the redundant CHECK constraint can be dropped since the
native NOT NULL constraint now exists.

Environment:

Source: PostgreSQL 13.23
Target: PostgreSQL 18.1
OS: CentOS9

I'm happy to provide additional details or test patches.