MIME-Version: 1.0
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Thu, 21 May 2026 17:14:07 +0500
Message-ID: 
 <CALdSSPgYCJu2AjUti9gnW9n7jfE-xZBDLA=n7N3JOQYCfUd+=g@mail.gmail.com>
Subject: Pg upgrade bug with NOT NULL NOT VALID
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CALdSSPgYCJu2AjUti9gnW9n7jfE-xZBDLA%3Dn7N3JOQYCfUd%2B%3Dg%40mail.gmail.com>
Precedence: bulk

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