public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Herrera <[email protected]>
To: Kirill Reshke <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Pg upgrade bug with NOT NULL NOT VALID
Date: Thu, 21 May 2026 10:17:58 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALdSSPgYCJu2AjUti9gnW9n7jfE-xZBDLA=n7N3JOQYCfUd+=g@mail.gmail.com>

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]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Pg upgrade bug with NOT NULL NOT VALID
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox