public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dilip Kumar <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
Date: Fri, 6 Feb 2026 16:16:08 +0530
Message-ID: <CAFiTN-uF8gF2cvHC_YbDMtBVeOWWtJc3LvCToy2PvLy9LJwC3g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAFiTN-vPxycE0YeNNTx6_nnD4cGHMP=WA=Yada+Ons=VTGPsdg@mail.gmail.com>
	<[email protected]>

On Fri, Feb 6, 2026 at 2:40 PM Laurenz Albe <[email protected]> wrote:
>
> On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
> > On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
  In-Reply-To: <CAFiTN-uF8gF2cvHC_YbDMtBVeOWWtJc3LvCToy2PvLy9LJwC3g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox