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

On Fri, Feb 6, 2026 at 2:40=E2=80=AFPM 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=E2=80=AFPM Laurenz Albe <laurenz.albe@cybe=
rtec.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 at=
tached patch
> > > > fixes the problem for me by avoiding given constraint names when ge=
nerating
> > > > 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=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.

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]=3D# CREATE TABLE two_not_null_constraints (
   col integer, CONSTRAINT two_not_null_constraints_col1_check CHECK (col >=
 5)
);
CREATE TABLE
postgres[58251]=3D# ALTER TABLE two_not_null_constraints ADD COLUMN col1
int check (col1 > 0);
ALTER TABLE
postgres[58251]=3D# \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


--=20
Regards,
Dilip Kumar
Google