public inbox for [email protected]
help / color / mirror / Atom feedRe: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
2+ messages / 2 participants
[nested] [flat]
* Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
@ 2026-02-06 07:23 Dilip Kumar <[email protected]>
2026-02-06 09:10 ` Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Dilip Kumar @ 2026-02-06 07:23 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]; [email protected]
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?
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
2026-02-06 07:23 Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Dilip Kumar <[email protected]>
@ 2026-02-06 09:10 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2026-02-06 09:10 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: [email protected]; [email protected]
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.
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-02-06 09:10 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-06 07:23 Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Dilip Kumar <[email protected]>
2026-02-06 09:10 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox