Subject: Pg upgrade bug with NOT NULL NOT VALID

From: Kirill Reshke <[email-masked]>
Date: Thu, 21 May 2026 17:14:07 +0500

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

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 21 May 2026 10:17:58 -0700

Hi,

On 2026-May-21, Kirill Reshke wrote:

> [...]

Hmm, interesting :-(

> 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-masked]

---

From: Kirill Reshke <[email-masked]>
Date: Sat, 23 May 2026 15:14:42 +0500

> Hi,

Hi, thank you for looking into this.

> 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?

> 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

---

From: Álvaro Herrera <[email-masked]>
Date: Sat, 23 May 2026 05:18:03 -0700

> 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.

> 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)