Horton

Thread

Commits

  1. Update .abi-compliance-history for AddRelationNotNullConstraints().

  2. Avoid name collision with NOT NULL constraints

  1. BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    The Post Office <noreply@postgresql.org> — 2026-02-04T17:33:21Z

    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.

  2. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-05T14:58:50Z

    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

  3. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-05T16:52:50Z

    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

  4. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Dilip Kumar <dilipbalaut@gmail.com> — 2026-02-06T07:23:33Z

    On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.

The fix LGTM. However I have one question, have you considered
validating the name selection logic for other constraint types as
well? I’m specifically thinking about AddRelationNewConstraints().
While I don't have a specific test case yet, is it possible for the
AddRelationNewConstraints to choose a name that is already in use when
adding a new column with a constraint?

-- 
Regards,
Dilip Kumar
Google

  5. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-06T09:10:31Z

    On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
> On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > 
> > 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.
> 
> The fix LGTM. However I have one question, have you considered
> validating the name selection logic for other constraint types as
> well? I’m specifically thinking about AddRelationNewConstraints().
> While I don't have a specific test case yet, is it possible for the
> AddRelationNewConstraints to choose a name that is already in use when
> adding a new column with a constraint?

Thanks for having a look.

I am not sure what you mean by "adding a new column": do you mean an
ALTER TABLE that runs after the CREATE TABLE?

The following works fine in v18:

  CREATE TABLE nulls (
     y integer UNIQUE,
     CONSTRAINT nulls_x_not_null FOREIGN KEY (y) REFERENCES nulls (y),
     CONSTRAINT nulls_x_fkey CHECK (TRUE)
  );

  ALTER TABLE nulls ADD x integer REFERENCES nulls (y) NOT NULL;

Both the new foreign key and the new NOT NULL constraint get a name
that doesn't conflict with the existing constraints.

But I don't claim that my patch fixes all possible problems during a
pg_upgrade.  If you define a table like this in v13:

  CREATE TABLE nulls (
     x integer UNIQUE NOT NULL,
     CONSTRAINT nulls_x_not_null FOREIGN KEY (x) REFERENCES nulls (x)
  );

then pg_dump --binary-upgrade will produce code like the following:

  CREATE TABLE laurenz.nulls (
      x integer NOT NULL
  );


  ALTER TABLE ONLY laurenz.nulls
      ADD CONSTRAINT nulls_x_key UNIQUE (x);

  ALTER TABLE ONLY laurenz.nulls
      ADD CONSTRAINT nulls_x_not_null FOREIGN KEY (x) REFERENCES laurenz.nulls(x);

and the last statement will cause an error, because the constraint name will
conflict with the name for the NOT NULL constraint.  In other words, my patch
only works for constraints that are dumped as part of the CREATE TABLE statement,
which I believe are only check constraints.

But my opinion is that it is very unlikely that anybody picks a name ending
in "_not_null" for a foreign key or unique constraint, while (as the bug report
demonstrates) there may be people who define (superfluous) check constraints
with such names.

So there is still the potential for pg_upgrade failures with my patch applied,
but it would fix the case most likely to occur in practice.

Yours,
Laurenz Albe

  6. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Dilip Kumar <dilipbalaut@gmail.com> — 2026-02-06T10:46:08Z

    On Fri, Feb 6, 2026 at 2:40 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
> > On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > >
> > > 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.
> >
> > The fix LGTM. However I have one question, have you considered
> > validating the name selection logic for other constraint types as
> > well? I’m specifically thinking about AddRelationNewConstraints().
> > While I don't have a specific test case yet, is it possible for the
> > AddRelationNewConstraints to choose a name that is already in use when
> > adding a new column with a constraint?
>
> Thanks for having a look.
>
> I am not sure what you mean by "adding a new column": do you mean an
> ALTER TABLE that runs after the CREATE TABLE?
>
> The following works fine in v18:
>
>   CREATE TABLE nulls (
>      y integer UNIQUE,
>      CONSTRAINT nulls_x_not_null FOREIGN KEY (y) REFERENCES nulls (y),
>      CONSTRAINT nulls_x_fkey CHECK (TRUE)
>   );
>
>   ALTER TABLE nulls ADD x integer REFERENCES nulls (y) NOT NULL;
>
> Both the new foreign key and the new NOT NULL constraint get a name
> that doesn't conflict with the existing constraints.

Right I see, I was talking about the similar case something like[1]
but I see it already handles the conflict and generates a conflicting
name if a constraint with the name already exists.  So we are good,
thanks.

postgres[58251]=# CREATE TABLE two_not_null_constraints (
   col integer, CONSTRAINT two_not_null_constraints_col1_check CHECK (col > 5)
);
CREATE TABLE
postgres[58251]=# ALTER TABLE two_not_null_constraints ADD COLUMN col1
int check (col1 > 0);
ALTER TABLE
postgres[58251]=# \d+ two_not_null_constraints
                                Table "public.two_not_null_constraints"
 Column |  Type   | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 col    | integer |           |          |         | plain   |
    |              |
 col1   | integer |           |          |         | plain   |
    |              |
Check constraints:
    "two_not_null_constraints_col1_check" CHECK (col > 5)
    "two_not_null_constraints_col1_check1" CHECK (col1 > 0)
Access method: heap


-- 
Regards,
Dilip Kumar
Google

  7. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-02-09T06:24:42Z

    Hi Laurenz,

Thanks for the patch and LGTM for PostgreSQL 18.

I tried to create following table on PG18

benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16385, 0,
two_not_null_constraints_col_not_null) already exists.

In PG17 I was able to create the table.

benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
CREATE TABLE
benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
DROP TABLE two_not_null_constraints;
-[ RECORD 1 ]----------------------------------
conname | two_not_null_constraints_col_not_null
contype | c


One question during the tests should we confirm the output of pg_constraint
table ? It would make sense during the tests but the current test is also
good to proceed.

benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
conname | contype
----------------------------------------+---------
two_not_null_constraints_col_not_null | n
two_not_null_constraints_col_not_null1 | c
(2 rows)

Laurenz Albe <laurenz.albe@cybertec.at>, 5 Şub 2026 Per, 17:52 tarihinde
şunu yazdı:

> 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
>

  8. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-09T11:01:27Z

    On Mon, 2026-02-09 at 07:24 +0100, Hüseyin Demir wrote:
> Thanks for the patch and LGTM for PostgreSQL 18.

Thanks for checking!

> I tried to create following table on PG18
> 
> benchmark=# CREATE TABLE two_not_null_constraints (
>      col integer NOT NULL,
>      CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
>   );
> ERROR:  duplicate key value violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
> DETAIL:  Key (conrelid, contypid, conname)=(16385, 0, two_not_null_constraints_col_not_null) already exists.
> 
> In PG17 I was able to create the table.

Yes, because what causes your problemm is a new feature in v18.

> One question during the tests should we confirm the output of pg_constraint table ?
> It would make sense during the tests but the current test is also good to proceed.

I would prefer not to, but I don't have a strong opinion about it.
Which name PostgreSQL chooses for the generated NOT NULL constraint
is not important, as long as it doesn't conflict with the existing name.

Yours,
Laurenz Albe

  9. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-02-09T17:16:26Z

    Hi,

> I would prefer not to, but I don't have a strong opinion about it.
> Which name PostgreSQL chooses for the generated NOT NULL constraint
> is not important, as long as it doesn't conflict with the existing name.

I also agree that it solves the problem in 18 which I already tested.
Thanks again for your help.


Laurenz Albe <laurenz.albe@cybertec.at>, 9 Şub 2026 Pzt, 12:01 tarihinde
şunu yazdı:

> On Mon, 2026-02-09 at 07:24 +0100, Hüseyin Demir wrote:
> > Thanks for the patch and LGTM for PostgreSQL 18.
>
> Thanks for checking!
>
> > I tried to create following table on PG18
> >
> > benchmark=# CREATE TABLE two_not_null_constraints (
> >      col integer NOT NULL,
> >      CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT
> NULL)
> >   );
> > ERROR:  duplicate key value violates unique constraint
> "pg_constraint_conrelid_contypid_conname_index"
> > DETAIL:  Key (conrelid, contypid, conname)=(16385, 0,
> two_not_null_constraints_col_not_null) already exists.
> >
> > In PG17 I was able to create the table.
>
> Yes, because what causes your problemm is a new feature in v18.
>
> > One question during the tests should we confirm the output of
> pg_constraint table ?
> > It would make sense during the tests but the current test is also good
> to proceed.
>
>
>
> Yours,
> Laurenz Albe
>

  10. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Michael Paquier <michael@paquier.xyz> — 2026-02-10T00:37:09Z

    On Fri, Feb 06, 2026 at 04:16:08PM +0530, Dilip Kumar wrote:
> Right I see, I was talking about the similar case something like[1]
> but I see it already handles the conflict and generates a conflicting
> name if a constraint with the name already exists.  So we are good,
> thanks.

I had this patch marked on my tablets for a lookup, with the hope that
it would have been possible to get something done for this week's
release, unfortunately I got drifted away and lacked time.  I'll look
into what you have here.
--
Michael

  11. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-02-20T06:52:33Z

    The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:       tested, failed
Spec compliant:           tested, failed
Documentation:            tested, failed

Hi,

I reviewed and tested v2 of this patch.

Builds with zero errors and zero warnings and all tests passed.

Verified the fix against Bug #19393. The original crash (unique key violation on pg_constraint_conrelid_contypid_conname_index) no longer occurs.
Also tested edge cases: cascading collisions (name, name1, name2 all taken), multiple NOT NULL columns with mixed collisions, and the normal no-collision case. All work
correctly. No doc changes needed.

The new status of this patch is: Ready for Committer

  12. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Álvaro Herrera <alvherre@kurilemu.de> — 2026-02-21T11:56:24Z

    On 2026-Feb-05, Laurenz Albe wrote:

> 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.

Thanks for this!  I have pushed it now to 18 and master (right before
the embargo for next week's release -- not really apologizing about
that, since this is clearly something that's going to bite users as they
move up to 18).  Two notes:

1. this will cause an ABI break report for AddRelationNotNullConstraints
in branch 18.  I considered the idea of adding a shim function
preserving the original API, but I think this is not a function likely
to be used by third-party code.  So I'll address this by adding an entry
to .abi-compliance-history instead.

2. I moved this foreach loop

> @@ -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;

from AddRelationNotNullConstraints to DefineRelation; it seems more
natural for the former to receive a list of constraint names than a list
of CookedConstraints.

Thanks Hüseyin for the report and Laurenz for the fix!

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it."         (ncm, http://lwn.net/Articles/174769/)

  13. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Álvaro Herrera <alvherre@kurilemu.de> — 2026-02-21T13:45:31Z

    On 2026-Feb-21, Álvaro Herrera wrote:
> 
> 1. this will cause an ABI break report for AddRelationNotNullConstraints
> in branch 18.  I considered the idea of adding a shim function
> preserving the original API, but I think this is not a function likely
> to be used by third-party code.  So I'll address this by adding an entry
> to .abi-compliance-history instead.

As expected, crake reported:

Leaf changes summary: 1 artifact changed
Changed leaf types summary: 0 leaf type changed
Removed/Changed/Added functions summary: 0 Removed, 1 Changed, 0 Added function (13 filtered out)
Removed/Changed/Added variables summary: 0 Removed, 0 Changed, 0 Added variable

1 function with incompatible sub-type changes: 

  [C] 'function List* AddRelationNotNullConstraints(Relation, List*, List*)' has some sub-type changes:
    parameter 4 of type 'List*' was added


I have pushed the update to .abi-compliance-history, and crake is now green.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)

  14. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-21T15:13:59Z

    On Sat, 2026-02-21 at 12:56 +0100, Álvaro Herrera wrote:
> > 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.
> 
> Thanks for this!  I have pushed it now to 18 and master (right before
> the embargo for next week's release -- not really apologizing about
> that, since this is clearly something that's going to bite users as they
> move up to 18).

Thank you, and thanks for the code improvements.

> 1. this will cause an ABI break report for AddRelationNotNullConstraints
> in branch 18.  I considered the idea of adding a shim function
> preserving the original API, but I think this is not a function likely
> to be used by third-party code.  So I'll address this by adding an entry
> to .abi-compliance-history instead.

I decided not to worry about changing the signature of that global function,
because it is only used in a single place and - like you - I deem it unlikely
to be useful elsewhere.

Yours,
Laurenz Albe

  15. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Michael Paquier <michael@paquier.xyz> — 2026-02-22T03:40:10Z

    On Sat, Feb 21, 2026 at 12:56:24PM +0100, Alvaro Herrera wrote:
> Thanks for this!  I have pushed it now to 18 and master (right before
> the embargo for next week's release -- not really apologizing about
> that, since this is clearly something that's going to bite users as they
> move up to 18).  Two notes:
> 
> 1. this will cause an ABI break report for AddRelationNotNullConstraints
> in branch 18.  I considered the idea of adding a shim function
> preserving the original API, but I think this is not a function likely
> to be used by third-party code.  So I'll address this by adding an entry
> to .abi-compliance-history instead.

While I get the feeling of urgency, I am wondering if this particular
fix should have been delayed and pushed for next May's release.  We
are already doing one quick release for the regressions found in the
CVE fixes..

Based on my read of the fix, I feel rather safe that this is OK.  But
as I have quoted upthread, the reason why I did not reply yet was to
wait for next week's release to be out before acting.  That's your
code of course, so no objections from here, just a slight doubt about
the timing.
--
Michael

  16. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Álvaro Herrera <alvherre@kurilemu.de> — 2026-02-23T16:47:46Z

    On 2026-Feb-22, Michael Paquier wrote:

> While I get the feeling of urgency, I am wondering if this particular
> fix should have been delayed and pushed for next May's release.  We
> are already doing one quick release for the regressions found in the
> CVE fixes..

I debated that with myself before pushing.  I thought the risk of
introducing a new bug or problem was pretty low; whereas the bug was
clearly already affecting real-world users in their attempts to migrate
to 18 from earlier versions, so the damage was real.

> Based on my read of the fix, I feel rather safe that this is OK.  But
> as I have quoted upthread, the reason why I did not reply yet was to
> wait for next week's release to be out before acting.  That's your
> code of course, so no objections from here, just a slight doubt about
> the timing.

Hmm, what?  The only response from you that I see in this thread is
https://postgr.es/m/aYp9tTWjUqS1ffAd@paquier.xyz
which was on Feb 10th, which is before the 18.2 release, so you couldn't
have been considering the 18.3 release yet.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X  - http://www.thelinuxreview.com/TUX/)

  17. Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

    Michael Paquier <michael@paquier.xyz> — 2026-02-23T23:20:11Z

    On Mon, Feb 23, 2026 at 05:47:46PM +0100, Alvaro Herrera wrote:
> On 2026-Feb-22, Michael Paquier wrote:
>> Based on my read of the fix, I feel rather safe that this is OK.  But
>> as I have quoted upthread, the reason why I did not reply yet was to
>> wait for next week's release to be out before acting.  That's your
>> code of course, so no objections from here, just a slight doubt about
>> the timing.
> 
> Hmm, what?  The only response from you that I see in this thread is
> https://postgr.es/m/aYp9tTWjUqS1ffAd@paquier.xyz
> which was on Feb 10th, which is before the 18.2 release, so you couldn't
> have been considering the 18.3 release yet.

Well, you are not counting the week of the 18.2 release, this thread
being an item I had on my stack of items to look at once the tags were
applied.  It didn't make to the top of the list.  It does not matter
much at the end.  Thanks for fixing the issue!
--
Michael