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

On Mon, Oct 19, 2026 at 6:31 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> Hi.
>
> While trying to check
> https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e
> potential pg_dump output related bug.
>
> I encountered the following issue first.
>
> I expect ALTER TABLE ADD CONSTRAINT to have the same effect as ALTER COLUMN SET
> NOT NULL, meaning that the column associated constraint will be marked as VALID
> and ENFORCED across the entire table inheritance hierarchy.
>
> I think it is ok for one form to fail (error out) while the other succeeds.
> However, if ALTER COLUMN SET NOT NULL succeeds, all associated NOT NULL
> constraints should be marked as VALID and ENFORCED.
>
> drop table if exists tp, tc cascade;
> create table tp (a int);
> create table tc (a int) inherits (tp);
> alter table tc add constraint nn not null a not valid;
> alter table tp add constraint nn not null a; -- error
> alter table tp alter column a set not null; -- should expect an error
>
> The last ALTER COLUMN SET NOT NULL should either error out or make the column
> 'a' in table "tc" have a not-null constraint that is ENFORCED and VALID?
>
> The partitioned table has a similar issue.
>
>
>
> --
> jian
> https://www.enterprisedb.com/
>
>

Hi Jian,

Thanks for the report. I reproduced the issue.

In ATExecSetNotNull(), the recursive path increments coninhcount for
an existing child constraint and returns without checking whether it
is valid. This can leave a NOT VALID child under a valid parent.

The attached patch rejects that combination, as ADD CONSTRAINT does.
It also fixes a related case where SET NOT NULL on an inherited-only
NOT VALID constraint makes it local but skips validation.

The new regression tests pass with the patch and fail on the
unpatched build. The full core regression suite passes (239/239)
on master cf765d6e145, tested on macOS arm64 with assertions enabled.

Please take a look.

Regards,
Haibo