Re: pg17 issues with not-null contraints

On 2024-Apr-18, Justin Pryzby wrote:

> That seems like it could be important.  I considered but never actually
> test your patch by pg_upgrading across major versions.

It would be a welcome contribution for sure.  I've been doing it rather
haphazardly, which is not great.

> BTW, this works up to v16 (although maybe it should not):
> 
> | CREATE TABLE ip(id int PRIMARY KEY); CREATE TABLE ic(id int) INHERITS (ip); ALTER TABLE ic ALTER id DROP NOT NULL;
> 
> Under v17, this fails. Maybe that's okay, but it should probably be
> called out in the release notes.

Sure, we should mention that.

> | ERROR:  cannot drop inherited constraint "ic_id_not_null" of relation "ic"
> 
> That's the issue that I mentioned in the 6 year old thread.  In the
> future (upgrading *from* v17) it won't be possible anymore, right?

Yeah, trying to drop the constraint in 17 fails as it should; it was one
of the goals of this whole thing in fact.

> It'd still be nice to detect the issue in advance rather than failing
> halfway through the upgrade.

Maybe we can have pg_upgrade --check look for cases we might have
trouble upgrading.  (I mean: such cases would fail if you have rows with
nulls in the affected columns, but the schema upgrade should be
successful.  Is that what you have in mind?)

> I have a rebased patch while I'll send on that thread.  I guess it's
> mostly unrelated to your patch but it'd be nice if you could take a
> look.

Okay.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Industry suffers from the managerial dogma that for the sake of stability
and continuity, the company should be independent of the competence of
individual employees."                                      (E. Dijkstra)