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
3+ messages / 3 participants
[nested] [flat]
* Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
@ 2026-02-06 10:46 Dilip Kumar <[email protected]>
2026-02-10 00:37 ` Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Michael Paquier <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Dilip Kumar @ 2026-02-06 10:46 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]; [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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
2026-02-06 10:46 Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Dilip Kumar <[email protected]>
@ 2026-02-10 00:37 ` Michael Paquier <[email protected]>
2026-02-20 06:52 ` Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Paquier @ 2026-02-10 00:37 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected]; [email protected]
On Fri, Feb 06, 2026 at 04:16:08PM +0530, Dilip Kumar wrote:
> 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.
I had this patch marked on my tablets for a lookup, with the hope that
it would have been possible to get something done for this week's
release, unfortunately I got drifted away and lacked time. I'll look
into what you have here.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
2026-02-06 10:46 Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Dilip Kumar <[email protected]>
2026-02-10 00:37 ` Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Michael Paquier <[email protected]>
@ 2026-02-20 06:52 ` Hüseyin Demir <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Hüseyin Demir @ 2026-02-20 06:52 UTC (permalink / raw)
To: [email protected]; +Cc: Laurenz Albe <[email protected]>
The following review has been posted through the commitfest application:
make installcheck-world: tested, failed
Implements feature: tested, failed
Spec compliant: tested, failed
Documentation: tested, failed
Hi,
I reviewed and tested v2 of this patch.
Builds with zero errors and zero warnings and all tests passed.
Verified the fix against Bug #19393. The original crash (unique key violation on pg_constraint_conrelid_contypid_conname_index) no longer occurs.
Also tested edge cases: cascading collisions (name, name1, name2 all taken), multiple NOT NULL columns with mixed collisions, and the normal no-collision case. All work
correctly. No doc changes needed.
The new status of this patch is: Ready for Committer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-20 06:52 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-06 10:46 Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists Dilip Kumar <[email protected]>
2026-02-10 00:37 ` Michael Paquier <[email protected]>
2026-02-20 06:52 ` Hüseyin Demir <[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