Thread

Re: Pg upgrade bug with NOT NULL NOT VALID

Álvaro Herrera <alvherre@kurilemu.de> — 2026-05-21T17:17:58Z

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/20050809113420.GD2768@phlogiston.dyndns.org