Message-ID: <99c37bcd620778c743a413dc9a1dce53ae9f4c38.camel@cybertec.at>
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when
 CHECK constraint named *_not_null exists
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: huseyin.d3r@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 06 Feb 2026 10:10:31 +0100
In-Reply-To: <CAFiTN-vPxycE0YeNNTx6_nnD4cGHMP=WA=Yada+Ons=VTGPsdg@mail.gmail.com>
References: <19393-6a82427485a744cf@postgresql.org>
	 <2b3a2cd5f9b6e82fba6ac45b413291b01dd252ea.camel@cybertec.at>
	 <61c535617992fff830961ecc09a9c20096bc1f36.camel@cybertec.at>
	 <CAFiTN-vPxycE0YeNNTx6_nnD4cGHMP=WA=Yada+Ons=VTGPsdg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/99c37bcd620778c743a413dc9a1dce53ae9f4c38.camel%40cybertec.at>
Precedence: bulk

On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
> On Thu, Feb 5, 2026 at 10:22=E2=80=AFPM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> >=20
> > On Thu, 2026-02-05 at 15:58 +0100, I wrote:
> > > The bug is actually not in pg_upgrade, but in CREATE TABLE.=C2=A0 The=
 attached patch
> > > fixes the problem for me by avoiding given constraint names when gene=
rating
> > > the names for NOT NULL constraints.
> >=20
> > ... and here is v2, including a regression test.
>=20
> The fix LGTM. However I have one question, have you considered
> validating the name selection logic for other constraint types as
> well? I=E2=80=99m 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.nu=
lls(x);

and the last statement will cause an error, because the constraint name wil=
l
conflict with the name for the NOT NULL constraint.  In other words, my pat=
ch
only works for constraints that are dumped as part of the CREATE TABLE stat=
ement,
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 re=
port
demonstrates) there may be people who define (superfluous) check constraint=
s
with such names.

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

Yours,
Laurenz Albe