Re: ALTER COLUMN SET NOT NULL differ from ALTER TABLE ADD CONSTRAINT

Hi Shihao,

Thanks for reviewing the patch!

On Wed, Oct 28, 2026 at 8:02 PM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> Hi Haibo,
>
> Thanks for the patch. I reviewed v1 and the main fix looks right to
> me. I think it needs to go back to 18.

Agreed. I'll work on the backpatch after we settle the fix on master.

>
> On 19 the wrong results go further. Take jian's case as a partitioned
> table, and add o(x int not null) with one row. Then
>
> select count(*) from o where x not in (select a from p);
>
> returns 1 on 19 and 0 on 18. The bug is from 18, so maybe it belongs
> under "Older bugs affecting stable branches" on the 19 open items page.
>

Thanks for the example. I reproduced the wrong result on master and
added a test for it. With the patch, SET NOT NULL fails before it can
leave the parent and partition in the inconsistent state.

> A few notes.
> 1. I ran the test before and after I believe it catched the bug, but 1k loc
> feels too much for me.

Agreed. I've reduced the added test and expected output from 1116
lines to 313 in v2.

>
> 2. with your change
> create table tp (a int);
> create table tc () inherits (tp);
> create table tg () inherits (tc);
> alter table tg add constraint tg_nn not null a not valid;
> alter table tp add constraint tp_nn not null a no inherit not valid;
> alter table tp alter constraint tp_nn inherit;
> -- ERROR: incompatible NOT VALID constraint "tg_nn" on relation "tg"
>
> INHERIT adds tp_nn to tc as a valid constraint, and then tg is
> rejected. Since tp_nn is NOT VALID, maybe the copy on tc should be
> NOT VALID too. ADD CONSTRAINT ... NOT VALID works that way.

That makes sense. I've changed v2 to create a NOT VALID constraint
on tc, so your example now succeeds. The patch still rejects a
VALID constraint being inherited by a NOT VALID child.

>
> Thanks,
> Shihao
>
>

The full core regression suite passes with v2 on master. Please
find the updated patch attached.

Thanks,
Haibo