ALTER COLUMN SET NOT NULL differ from ALTER TABLE ADD CONSTRAINT
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254273psql -h localhost -U postgresBuilt from patchset v3 (message #3), October 26, 2026 at 09:43 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254273_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254273_3 && git checkout t254273_3Patchset v3 (message #3) is on t254273_3
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.
Hello Jian,
Following up on the issue identified during the investigation of the
potential pg_dump output-related bug.
I wanted to highlight one additional concern regarding the behavior of ALTER
TABLE ... ALTER COLUMN ... SET NOT NULL when a descendant table has an
existing NOT VALID NOT NULL constraint.
In the inheritance example provided earlier, should this operation fail, or
should it ensure that the corresponding constraint on the child table is
also marked as valid and enforced?
I noticed that a similar issue appears to exist with partitioned tables as
well.
Could you please clarify whether this behavior is expected or whether it
should be addressed as part of the existing fix?
Thanks!
Osama Abdul Qader
On Mon, 19 Oct 2026 at 19:01, jian he <jian.universality@gmail.com> wrote:
Show quoted text
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 errorThe 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.
On Mon, Oct 19, 2026 at 6:31 AM jian he <jian.universality@gmail.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 errorThe 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.
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