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

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.

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