public inbox for [email protected]
help / color / mirror / Atom feedPg upgrade bug with NOT NULL NOT VALID
4+ messages / 2 participants
[nested] [flat]
* Pg upgrade bug with NOT NULL NOT VALID
@ 2026-05-21 12:14 Kirill Reshke <[email protected]>
2026-05-21 17:17 ` Re: Pg upgrade bug with NOT NULL NOT VALID Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Kirill Reshke @ 2026-05-21 12:14 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
On pg 17:
create table t(i int not null);
alter table t add constraint t_i_not_null check((i is not null)) not valid;
then upgrade to current master, will fail with
```
pg_restore: error: could not execute query: ERROR: constraint
"t_i_not_null" for relation "t" already exists
Command was: ALTER TABLE "public"."t"
ADD CONSTRAINT "t_i_not_null" CHECK (("i" IS NOT NULL)) NOT VALID;
```
I think we need to fix this in the spirit of [0]. I'm currently
thinking of choosing a less obvious name for NOT NULL constraint that
is created during CREATE TABLE processing. Is that a good way to
address this? This will impact non-pg_upgrade creations too, is this
important?
If so, I will share a patch on this
[0] https://git.postgresql.org/cgit/postgresql.git/commit/?id=3db61db48ef5b8898f7e85f98548fdec79d76524
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Pg upgrade bug with NOT NULL NOT VALID
2026-05-21 12:14 Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
@ 2026-05-21 17:17 ` Álvaro Herrera <[email protected]>
2026-05-23 10:14 ` Re: Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Álvaro Herrera @ 2026-05-21 17:17 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi,
On 2026-May-21, Kirill Reshke wrote:
> On pg 17:
>
> create table t(i int not null);
> alter table t add constraint t_i_not_null check((i is not null)) not valid;
> [...]
Hmm, interesting :-(
> I think we need to fix this in the spirit of [0]. I'm currently
> thinking of choosing a less obvious name for NOT NULL constraint that
> is created during CREATE TABLE processing. Is that a good way to
> address this?
I don't think so, because any name you choose mechanically can be chosen
by the user for their check constraint, so you will be making the
constraint name significantly worse in all cases while not giving any
hard assurances that you've fully fixed the problem, just moved it
around.
I see two alternatives. One is to have pg_dump --binary-upgrade choose
a constraint name for the not-null with full knowledge of all other
constraint names, so that we know to generate a non conflicting one.
I suspect this is not easy to code.
The other is much simpler: make pg_upgrade -c warn you about the check
constraint name so that you know to rename it before the upgrade. This
should be fairly trivial. I think the only somewhat ugly thing about
this is that we'd need to match ChooseConstraintName more closely in the
cases of overly long table and column names. The current algorithm we
have to generate constraint names on the pg_dump side for not-null
constraints is naive because it doesn't matter if it gets it slightly
wrong in those border cases; but in this case it would matter.
Thanks,
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"You're _really_ hosed if the person doing the hiring doesn't understand
relational systems: you end up with a whole raft of programmers, none of
whom has had a Date with the clue stick." (Andrew Sullivan)
https://postgr.es/m/[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Pg upgrade bug with NOT NULL NOT VALID
2026-05-21 12:14 Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
2026-05-21 17:17 ` Re: Pg upgrade bug with NOT NULL NOT VALID Álvaro Herrera <[email protected]>
@ 2026-05-23 10:14 ` Kirill Reshke <[email protected]>
2026-05-23 12:18 ` Re: Pg upgrade bug with NOT NULL NOT VALID Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Kirill Reshke @ 2026-05-23 10:14 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Thu, 21 May 2026 at 22:18, Álvaro Herrera <[email protected]> wrote:
>
> Hi,
>
Hi, thank you for looking into this.
> I see two alternatives. One is to have pg_dump --binary-upgrade choose
> a constraint name for the not-null with full knowledge of all other
> constraint names, so that we know to generate a non conflicting one.
> I suspect this is not easy to code.
>
Well, for this option, we need to be told about what other constraint
names that are about to be created. So, pg_dump will need to issue an
SQL that says: please create this relation, but also never choose
name1 to anything in the process. I guess this is not committable...
Maybe you can clarify the design here?
> The other is much simpler: make pg_upgrade -c warn you about the check
> constraint name so that you know to rename it before the upgrade.
I don't think this is good when the database asks you to change your
DDL because of its internal troubles with something.
I also think we should make pg_upgrade just work in this case.
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Pg upgrade bug with NOT NULL NOT VALID
2026-05-21 12:14 Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
2026-05-21 17:17 ` Re: Pg upgrade bug with NOT NULL NOT VALID Álvaro Herrera <[email protected]>
2026-05-23 10:14 ` Re: Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
@ 2026-05-23 12:18 ` Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Álvaro Herrera @ 2026-05-23 12:18 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 2026-May-23, Kirill Reshke wrote:
> On Thu, 21 May 2026 at 22:18, Álvaro Herrera <[email protected]> wrote:
> > I see two alternatives. One is to have pg_dump --binary-upgrade choose
> > a constraint name for the not-null with full knowledge of all other
> > constraint names, so that we know to generate a non conflicting one.
> > I suspect this is not easy to code.
>
> Well, for this option, we need to be told about what other constraint
> names that are about to be created.
Sure. Of course, pg_dump can run all the queries it needs to obtain
constraint names, so it's certainly doable.
> > The other is much simpler: make pg_upgrade -c warn you about the check
> > constraint name so that you know to rename it before the upgrade.
>
> I don't think this is good when the database asks you to change your
> DDL because of its internal troubles with something.
OK. I disagree but I'm not opposed if you want to do it the other way.
If you prefer that I write the patch, that's fine too.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-05-23 12:18 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-21 12:14 Pg upgrade bug with NOT NULL NOT VALID Kirill Reshke <[email protected]>
2026-05-21 17:17 ` Álvaro Herrera <[email protected]>
2026-05-23 10:14 ` Kirill Reshke <[email protected]>
2026-05-23 12:18 ` Álvaro Herrera <[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