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

On Fri, Feb 6, 2026 at 2:40 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(dot)albe(at)cybertec(dot)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