[PG19] Three bugs with a CHECK constraint that only the child enforces
Hi hackers,
I used Opus to analyze the new features in PG19, and it found three
bugs with a CHECK constraint that a child enforces and its parent
does not. Each item has a script and a patch with its number.
"Before" is 19 and master today. "After" is with the patch, and is
the output I expect.
1. ALTER CONSTRAINT ... ENFORCED on the parent does not check a child
whose constraint is ENFORCED but NOT VALID. The parent is marked
validated anyway, so a query on it can miss the child's bad row.
create table p (a int, constraint c check (a > 0) not enforced);
create table k (a int);
insert into k values (-1);
alter table k add constraint c check (a > 0) not valid;
alter table k inherit p;
alter table p alter constraint c enforced;
select count(*) from (select a from p union all select 1) s
where a < 0;
Before:
ALTER TABLE
count
-------
0
After 0001:
ERROR: check constraint "c" of relation "k" is violated by some row
count
-------
1
2. ALTER COLUMN TYPE on the parent turns the child's constraint back
to NOT ENFORCED. The child's constraint is dropped and recreated from
the parent's definition.
create table p2 (a int, constraint c check (a > 0) not enforced);
create table k2 () inherits (p2);
alter table k2 alter constraint c enforced;
alter table p2 alter column a type bigint;
insert into k2 values (-1);
Before:
INSERT 0 1
After 0002:
ERROR: new row for relation "k2" violates check constraint "c"
DETAIL: Failing row contains (-1).
3. pg_dump prints only the parent's definition, so the child is NOT
ENFORCED after a restore. pg_upgrade is fine.
create table p3 (a int, constraint c check (a > 0) not enforced);
create table k3 () inherits (p3);
alter table k3 alter constraint c enforced;
Before, pg_dump -s has:
ALTER TABLE public.p3
ADD CONSTRAINT c CHECK ((a > 0)) NOT ENFORCED;
After 0003, it also has:
ALTER TABLE public.k3 ALTER CONSTRAINT c ENFORCED;
1 is new in 19. 2 and 3 are easy to hit in 19, but they are also in
18, see v18-forms.sh. 0002 and 0003 use the new ALTER CONSTRAINT, so
they do not apply to 18 as they are. I think these belong on the open
items list.
Nikolay has a patch for a nearby case in [1]/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com, ADD CONSTRAINT on a
child. These three are separate. 0001 touches the same function, so
one of the two will need a rebase.
[1]: /messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
Thanks,
Shihao
On Oct 9, 2026, at 12:42, shihao zhong <zhong950419@gmail.com> wrote:
Hi hackers,
I used Opus to analyze the new features in PG19, and it found three
bugs with a CHECK constraint that a child enforces and its parent
does not. Each item has a script and a patch with its number.
Forgot to attach the patches?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Hi Chao,
Forgot to attach the patches?
Yea...... Patches are attached.
Thanks,
Shihao
Attachments:
t254121_3v1-0003-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchapplication/octet-stream; name=v1-0003-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchDownload+102-6
v1-0001-Validate-NOT-VALID-children-when-a-parent-CHECK-b.patchapplication/octet-stream; name=v1-0001-Validate-NOT-VALID-children-when-a-parent-CHECK-b.patchDownload+49-4
v1-0002-Keep-a-child-s-ENFORCED-CHECK-when-the-parent-s-c.patchapplication/octet-stream; name=v1-0002-Keep-a-child-s-ENFORCED-CHECK-when-the-parent-s-c.patchDownload+54-1
On Fri, Oct 9, 2026 at 12:42 PM shihao zhong <zhong950419@gmail.com> wrote:
Hi hackers,
I used Opus to analyze the new features in PG19, and it found three
bugs with a CHECK constraint that a child enforces and its parent
does not. Each item has a script and a patch with its number.
"Before" is 19 and master today. "After" is with the patch, and is
the output I expect.1. ALTER CONSTRAINT ... ENFORCED on the parent does not check a child
whose constraint is ENFORCED but NOT VALID. The parent is marked
validated anyway, so a query on it can miss the child's bad row.create table p (a int, constraint c check (a > 0) not enforced);
create table k (a int);
insert into k values (-1);
alter table k add constraint c check (a > 0) not valid;
alter table k inherit p;
alter table p alter constraint c enforced;
select count(*) from (select a from p union all select 1) s
where a < 0;
Before:
ALTER TABLE
count
-------
0
After 0001:
ERROR: check constraint "c" of relation "k" is violated by some row
count
-------
1
Here I think we need to validate table k; otherwise the parent ends up
valid while the child is not valid.
However, ALTER CONSTRAINT ENFORCED does not validate a constraint that
is already ENFORCED NOT VALID.
So the behavior becomes inconsistent:
ALTER CONSTRAINT ENFORCED would validate ENFORCED NOT VALID constraint
when it reaches it through the parent,
but not when it is run on that constraint directly.
The following demo yields no error in PG18.
----------------------
begin;
drop table if exists pktable, fktable;
CREATE TABLE pktable ( ptest1 int PRIMARY KEY, ptest2 text );
CREATE TABLE fktable ( ftest1 int, ftest2 int);
insert into fktable values (1, 2);
alter table fktable add CONSTRAINT fk1 foreign key (ftest1) REFERENCES
PKTABLE(ptest1) NOT valid;
alter table fktable alter constraint fk1 enforced;
rollback;
----------------------
2. ALTER COLUMN TYPE on the parent turns the child's constraint back
to NOT ENFORCED. The child's constraint is dropped and recreated from
the parent's definition.create table p2 (a int, constraint c check (a > 0) not enforced);
create table k2 () inherits (p2);
alter table k2 alter constraint c enforced;
alter table p2 alter column a type bigint;
insert into k2 values (-1);
Before:
INSERT 0 1
After 0002:
ERROR: new row for relation "k2" violates check constraint "c"
DETAIL: Failing row contains (-1).
This is a pre-existing bug/behavior: when a child table's constraint is in a
different state from the parent's (for example, ENFORCED on the child but
NOT ENFORCED on the parent), ALTER COLUMN ... SET DATA TYPE and ALTER
COLUMN ... SET EXPRESSION recreate the child's constraint from the
parent's definition, so the child silently ends up with the parent's
state.
See the demo below in pg18.
begin;
drop table if exists pp, pp1;
create table pp (k int, f1 int generated always as (1) stored)
partition by list (k);
alter table pp add constraint c check (f1 > 0) not enforced;
create table pp1 (k int, f1 int generated always as (1) stored
constraint c check (f1 > 0));
alter table pp attach partition pp1 for values in (1);
alter table pp alter column f1 type bigint;
\d+ pp1
rollback;
--------------------------------
On Oct 9, 2026, at 12:42, shihao zhong <zhong950419@gmail.com> wrote:
Hi hackers,
I used Opus to analyze the new features in PG19, and it found three
bugs with a CHECK constraint that a child enforces and its parent
does not. Each item has a script and a patch with its number.
"Before" is 19 and master today. "After" is with the patch, and is
the output I expect.1. ALTER CONSTRAINT ... ENFORCED on the parent does not check a child
whose constraint is ENFORCED but NOT VALID. The parent is marked
validated anyway, so a query on it can miss the child's bad row.create table p (a int, constraint c check (a > 0) not enforced);
create table k (a int);
insert into k values (-1);
alter table k add constraint c check (a > 0) not valid;
alter table k inherit p;
alter table p alter constraint c enforced;
select count(*) from (select a from p union all select 1) s
where a < 0;
Before:
ALTER TABLE
count
-------
0
After 0001:
ERROR: check constraint "c" of relation "k" is violated by some row
count
-------
1
I can reproduce the bug, and the fix in 0001 looks good.
However, this bug report raised a concern that I didn’t notice while working on my previous enforceability fix [1]/messages/by-id/E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com.
This bug report relies on ALTER TABLE p ALTER CONSTRAINT c ENFORCED also changing the child table k’s constraint from NOT VALID to VALID.
I verified the following on PG18:
```
evantest=# create table t(a int);
CREATE TABLE
evantest=# insert into t values (-1);
INSERT 0 1
evantest=# alter table t add constraint c check (a>0) enforced not valid;
ALTER TABLE
evantest=#
evantest=# select conenforced, convalidated from pg_constraint where conname='c';
conenforced | convalidated
-------------+--------------
t | f
(1 row)
```
The test shows that a CHECK constraint can be both ENFORCED and NOT VALID. This tests adding a constraint, however, rather than changing its enforceability.
Returning to your reproducer, after altering k to inherit from p, k’s constraint c remains NOT VALID:
```
evantest=# create table p (a int, constraint c check (a > 0) not enforced);
CREATE TABLE
evantest=# create table k (a int);
CREATE TABLE
evantest=# insert into k values (-1);
INSERT 0 1
evantest=# alter table k add constraint c check (a > 0) not valid;
ALTER TABLE
evantest=# alter table k inherit p;
ALTER TABLE
evantest=# select conrelid::regclass, conenforced, convalidated from pg_constraint where conname='c';
conrelid | conenforced | convalidated
----------+-------------+--------------
p | f | f
k | t | f
(2 rows)
```
So the design question here is whether changing a constraint from NOT ENFORCED to ENFORCED should automatically validate it.
I read through the original discussion at [2]/messages/by-id/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com. It explicitly mentions validating existing rows, but I didn’t find a discussion of whether NOT VALID should be retained. That might be reasonable, because the original patch followed the existing behavior for changing foreign key constraints to ENFORCED.
IMO, changing enforceability should retain NOT VALID. However, I understand that the current implementation validates existing rows, so this patch fixes a bug under those semantics. My concern is with the original design, and it may be too late to reopen that discussion at this point. I will create a separate thread for v20.
[1]: /messages/by-id/E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com
[2]: /messages/by-id/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Fri, Oct 9, 2026 at 12:42 PM shihao zhong <zhong950419@gmail.com> wrote:
Hi hackers,
I used Opus to analyze the new features in PG19, and it found three
bugs with a CHECK constraint that a child enforces and its parent
does not. Each item has a script and a patch with its number.
"Before" is 19 and master today. "After" is with the patch, and is
the output I expect.3. pg_dump prints only the parent's definition, so the child is NOT
ENFORCED after a restore. pg_upgrade is fine.create table p3 (a int, constraint c check (a > 0) not enforced);
create table k3 () inherits (p3);
alter table k3 alter constraint c enforced;
Before, pg_dump -s has:
ALTER TABLE public.p3
ADD CONSTRAINT c CHECK ((a > 0)) NOT ENFORCED;
After 0003, it also has:
ALTER TABLE public.k3 ALTER CONSTRAINT c ENFORCED;
Hi.
Let pg_dump produce ALTER CONSTRAINT seems weird.
We can let pg_dump produce inline constraint (within CREATE TABLE
statement) or ALTER TABLE ADD CONSTRAINT, see the attached.
Attachments:
v2-0001-pg_dump-keep-child-table-CHECK-constraint-status.txttext/plain; charset=US-ASCII; name=v2-0001-pg_dump-keep-child-table-CHECK-constraint-status.txtDownload+139-4
i jian, Chao,
Thanks for the reviews.
1. jian, you are right, that was inconsistent. In v2, ALTER
CONSTRAINT ... ENFORCED on the parent fails with "conflicts with NOT
VALID constraint on child table", the error that ALTER TABLE ...
INHERIT already gives here. The user validates the child first. So
nothing becomes valid behind the user's back, and this does not
depend on the question Chao raised.
2. Yes, 18 has it too, the first mail said so. 0002 fixes your
partition example as well.
3. Dumping the child's constraint as local changes the catalog. With
your patch, k's c comes back with conislocal = true, so ALTER TABLE p
DROP CONSTRAINT c no longer drops it from k. pg_upgrade keeps it
inherited today, and your patch makes it local there too. ALTER
CONSTRAINT is the only command that gives back an inherited
constraint that only the child enforces.
Your partition case is real, my 0003 missed it, and pg_upgrade loses
it on master too. v2-0003 dumps that constraint before its ancestors'
ones, as your patch does, with your test. I added you as co-author.
A local NOT VALID child that sorts after the parent still comes back
validated. That comes from ADD CONSTRAINT merging into the inherited
NOT ENFORCED copy, which Nikolay's patch fixes [1]/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com.
[1]: /messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
Thanks,
Shihao
Attachments:
t254121_7v2-0002-Keep-a-child-s-ENFORCED-CHECK-when-the-parent-s-c.patchapplication/octet-stream; name=v2-0002-Keep-a-child-s-ENFORCED-CHECK-when-the-parent-s-c.patchDownload+54-1
v2-0003-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchapplication/octet-stream; name=v2-0003-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchDownload+159-8
v2-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-.patchapplication/octet-stream; name=v2-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-.patchDownload+47-1
On Thu, Oct 15, 2026 at 11:48 AM shihao zhong <zhong950419@gmail.com> wrote:
3. Dumping the child's constraint as local changes the catalog. With
your patch, k's c comes back with conislocal = true, so ALTER TABLE p
DROP CONSTRAINT c no longer drops it from k. pg_upgrade keeps it
inherited today, and your patch makes it local there too. ALTER
CONSTRAINT is the only command that gives back an inherited
constraint that only the child enforces.
/messages/by-id/202410011520.zl4go2gzgzwn@alvherre.pgsql
mentioned in some case, it's ok for pg_dump changing conislocal status.
Maybe it does not apply to this case.
However, my guess is that for the SQL below, some constraint is bound
to change conislocal.
because adding not valid constraint happens very later of pg_dump.
drop table if exists p3 cascade;
create table p3 (a int);
alter table p3 add constraint c check (a > 0) not valid;
create table k3 () inherits (p3);
Your partition case is real, my 0003 missed it, and pg_upgrade loses
it on master too. v2-0003 dumps that constraint before its ancestors'
ones, as your patch does, with your test. I added you as co-author.A local NOT VALID child that sorts after the parent still comes back
validated. That comes from ADD CONSTRAINT merging into the inherited
NOT ENFORCED copy, which Nikolay's patch fixes [1].[1] /messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
There are 2 more bugs related after playing around with your v2-0003.
Bug A affects PG18 and earlier (reproduced on PG16).
The following scirpt pg_dump output is wrong.
drop table if exists p3 cascade;
create table p3 (a int);
alter table p3 add constraint c check (a > 0) not valid;
create table k3 () inherits (p3);
alter table k3 validate constraint c;
----------------------------------------------------
Bug B affects PG18.
Restoring the pg_dump output of the script below into an existing cluster fails.
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 alter column a set not null;
BUGB root cause is
https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e
I am using
pg_dump --table-and-children=pattern --no-owner --no-acl --clean --no-data
for testing.
Since these bugs are related, posting them in this thread seems appropriate.
----------------------------------------------------------------------------------------
2. ALTER COLUMN TYPE on the parent turns the child's constraint back
to NOT ENFORCED. The child's constraint is dropped and recreated from
the parent's definition.
This issue dates back to at least PG14.
See https://dbfiddle.uk/AO112Bxp.
I think we need a unified way to restore the old constraint's properties.
The attached WIP patch only makes ALTER COLUMN SET DATA TYPE and ALTER COLUMN
SET EXPRESSION preserve pg_constraint.conenforced and convalidated of a
child's constraint. It does not try to preserve any other constraint
properties.
Since this is an old bug, I tend to think it's not an open item for PG19.
Attachments:
v3-0001-Preserve-child-constraint-property-in-ALTER-TYPE-and-SET-EXPRESSION.txttext/plain; charset=US-ASCII; name=v3-0001-Preserve-child-constraint-property-in-ALTER-TYPE-and-SET-EXPRESSION.txtDownload+885-6
Hi jian,
Thanks for v3. It passes my 0002 test, so I will drop 0002 and leave
2 to your patch. 0001 and 0003 still apply on top of v3, and the
tests pass.
v3 also fixes a wrong result that 18 and 19 have:
create table p (b int, constraint c check (b > 0) not enforced);
create table k (b int);
insert into k values (-1);
alter table k add constraint c check (b > 0) not valid;
alter table k inherit p;
alter table p alter column b type bigint;
select count(*) from p where b < 0;
This returns 0. The ALTER TYPE marks the child's NOT VALID constraint
as validated, so the planner skips k. The one line change in
MergeWithExistingConstraint() from Nikolay's patch [1]/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com fixes it on 18
and 19.
In your p3 example only convalidated changes after a restore, not
conislocal. I think this is your Bug A. I would leave it out of this set.
Bug B reproduces on 18 and later. It is about NOT NULL, so maybe it
needs its own thread.
[1]: /messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
Thanks,
Shihao
On Thu, Oct 15, 2026 at 11:48 AM shihao zhong <zhong950419@gmail.com> wrote:
3. Dumping the child's constraint as local changes the catalog. With
your patch, k's c comes back with conislocal = true, so ALTER TABLE p
DROP CONSTRAINT c no longer drops it from k. pg_upgrade keeps it
inherited today, and your patch makes it local there too. ALTER
CONSTRAINT is the only command that gives back an inherited
constraint that only the child enforces.
Hi.
v2-0001 looks ok, but I added an errhint and a partitioned table test
case, see v4-0001.
Command ALTER COLUMN SET NOT NULL, which will call ATExecSetNotNull.
ATExecSetNotNull in some case will change existing pg_constraint.conislocal.
Similarly, perhaps we can let ALTER CONSTRAINT ENFORCED change the
conislocal too.
The attached alternative is let ALTER CONSTRAINT ENFORCED change the
pg_constraint.conislocal
for table inheritance (not partition inheritance).
------------
drop table if exists p3 cascade;
create table p3 (a int, constraint c check (a > 0) not enforced);
create table k3 () inherits (p3);
alter table k3 alter constraint c enforced;
The above command, the new pg_dump output is
CREATE TABLE public.p3 (a integer);
CREATE TABLE public.k3 ( CONSTRAINT c CHECK ((a > 0)) ) INHERITS (public.p3);
ALTER TABLE public.p3 ADD CONSTRAINT c CHECK ((a > 0)) NOT ENFORCED;
------------------------------------
The motivation for this alternative is restore performance.
when pg_dump emits ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED.
This requires an extra full scan of the child table under an ACCESS
EXCLUSIVE lock.
For large tables, i am worried that this extra table scan may slow
down the pg_restore.
Let me know what you think of this alternative.
Attachments:
v4-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-child.txttext/plain; charset=US-ASCII; name=v4-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-child.txtDownload+73-1
v4-0002-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHECK-constraint.txttext/plain; charset=US-ASCII; name=v4-0002-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHECK-constraint.txtDownload+164-5
Hi jian,
v4 looks good to me. One case is still lost after dump and restore,
on master and 19, and also on 18:
create table p1 (a int, constraint c check (a > 0));
create table p2 (a int, constraint c check (a > 0) not enforced);
create table k () inherits (p1, p2);
alter table k no inherit p1;
k's c is not local here, so it comes back NOT ENFORCED. I think
pg_dump still needs the ALTER CONSTRAINT ... ENFORCED from my v2-0003
for this case.
Thanks,
Shihao
On Tue, Oct 20, 2026 at 10:24 AM shihao zhong <zhong950419@gmail.com> wrote:
Hi jian,
v4 looks good to me. One case is still lost after dump and restore,
on master and 19, and also on 18:create table p1 (a int, constraint c check (a > 0));
create table p2 (a int, constraint c check (a > 0) not enforced);
create table k () inherits (p1, p2);
alter table k no inherit p1;k's c is not local here, so it comes back NOT ENFORCED.
Great!
mimic the above for convalidated, we can easily find out a very old
pg_dump bug. (at least PG16)
drop table if exists p1, p2, k cascade;
create table p1 (a int, constraint c check (a > 0));
create table p2 (a int);
alter table p2 add constraint c check (a > 0) not valid;
create table k () inherits (p1, p2);
alter table k no inherit p1;
Table k comes back as NOT VALID, but it should be valid.
To preserve conislocal and convalidated, pg_dump need emit a
ALTER TABLE public.k VALIDATE CONSTRAINT c;
*after*
ALTER TABLE public.p2
ADD CONSTRAINT c CHECK ((a > 0)) NOT VALID;
Regarding pg_constraint.conislocal, we already have inconsistency, see
function flagInhAttrs.
/*
* For versions >18, we must print the not-null constraint locally
* for this table even if it isn't really locally defined, but is
* valid for the child and no parent has it as valid.
*/
if (fout->remoteVersion >= 180000 && allNotNullsInvalid)
tbinfo->notnull_islocal[j] = true;
Not preserving pg_constraint.conislocal in pg_dump would surely make
life easier.
Related thread:
/messages/by-id/202504281450.4powv4cchler@alvherre.pgsql
Now we kow the visible consequences: after pg_dump, ALTER TABLE DROP
CONSTRAINT will no longer cascade to the child table's constraint.
Hi jian,
Here is v5. 0001 and 0002 are your v4. 0003 dumps the child's
ENFORCED CHECK as local when no parent enforces it, as you suggested.
0003 skips NOT VALID constraints. Restoring one would mark it valid
without a scan, the merge bug in Nikolay's thread [1]/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com.
Your convalidated case goes back to 14. Could you post it as its own
patch? I am happy to review.
Thanks,
Shihao
[1]: /messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
/messages/by-id/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
Attachments:
t254121_13v5-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-.patchapplication/octet-stream; name=v5-0001-Reject-enforcing-a-parent-CHECK-over-a-NOT-VALID-.patchDownload+73-1
v5-0002-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchapplication/octet-stream; name=v5-0002-pg_dump-keep-ENFORCED-on-a-child-s-inherited-CHEC.patchDownload+164-5
v5-0003-pg_dump-keep-ENFORCED-on-a-child-s-CHECK-that-no-.patchapplication/octet-stream; name=v5-0003-pg_dump-keep-ENFORCED-on-a-child-s-CHECK-that-no-.patchDownload+61-3
On Sat, Oct 24, 2026 at 12:00 PM shihao zhong <zhong950419@gmail.com> wrote:
Here is v5. 0001 and 0002 are your v4. 0003 dumps the child's
ENFORCED CHECK as local when no parent enforces it, as you suggested.
V5-0001 looks good to me.
I was just pointing out that there is a precedent for pg_dump restoring a
constraint that has (conislocal = false) as one with (conislocal = true).
I'm less sure this is the right thing to do for regular table inheritance, so
let's see what others think. For partitions, though, I believe this is
what we need to do.
While experimenting, I also noticed that the following code in
src/backend/catalog/heap.c has no equivalent for NOT NULL constraints:
/*
* In case of partitions, an inherited constraint must be inherited
* only once since it cannot have multiple parents and it is never
* considered local.
*/
if (rel->rd_rel->relispartition)
{
con->coninhcount = 1;
con->conislocal = false;
}
If NOT NULL constraints were handled exactly like CHECK constraints in the
code above, the NOT NULL constraint on table ppnn_1 below would end up with
(conislocal = false).
CREATE TABLE public.ppnn (col1 integer) PARTITION BY LIST (col1);
CREATE TABLE public.ppnn_1 (col1 integer CONSTRAINT nn NOT NULL);
ALTER TABLE public.ppnn ATTACH PARTITION public.ppnn_1 FOR VALUES IN (1);
ALTER TABLE public.ppnn ADD CONSTRAINT nn NOT NULL col1 NOT VALID;
Should the NOT NULL constraint on ppnn_1 have conislocal = false?
It's related to pg_constraint.conislocal behavior, so I am posting here too.
----------------------------------------------------------------------------------------------------------------------------------
To summarize where this thread:
v5-0001 is a fix for PG19.
v5-0002 and v5-0003 fix the problem for PG18 as well. However, they
require us to accept that pg_dump may restore a child's inherited CHECK
constraint with pg_constraint.conislocal = true, even though it was false
in the original database.
The visible consequence is that, after a
restore, ALTER TABLE ... DROP CONSTRAINT on the parent no longer drops the
child's copy of the constraint; the child keeps it as a local constraint.
(Given table inheritance is not liked by many people, maybe it's OK to
change conislocal for table inheritance, but not partitioning
inheritance)
On 2026-10-29 Th 1:05 AM, jian he wrote:
To summarize where this thread:
v5-0001 is a fix for PG19.
v5-0002 and v5-0003 fix the problem for PG18 as well. However, they
require us to accept that pg_dump may restore a child's inherited CHECK
constraint with pg_constraint.conislocal = true, even though it was false
in the original database.
The visible consequence is that, after a
restore, ALTER TABLE ... DROP CONSTRAINT on the parent no longer drops the
child's copy of the constraint; the child keeps it as a local constraint.
(Given table inheritance is not liked by many people, maybe it's OK to
change conislocal for table inheritance, but not partitioning
inheritance)
Yeah, I'm going to commit patch 0001, which is pretty straightforward,
to close the pg19 open item, and leave the remaining items for later
discussion.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 2026-10-30 Fr 5:43 PM, Andrew Dunstan wrote:
On 2026-10-29 Th 1:05 AM, jian he wrote:
To summarize where this thread:
v5-0001 is a fix for PG19.
v5-0002 and v5-0003 fix the problem for PG18 as well. However, they
require us to accept that pg_dump may restore a child's inherited CHECK
constraint with pg_constraint.conislocal = true, even though it was
false
in the original database.
The visible consequence is that, after a
restore, ALTER TABLE ... DROP CONSTRAINT on the parent no longer
drops the
child's copy of the constraint; the child keeps it as a local
constraint.
(Given table inheritance is not liked by many people, maybe it's OK to
change conislocal for table inheritance, but not partitioning
inheritance)Yeah, I'm going to commit patch 0001, which is pretty straightforward,
to close the pg19 open item, and leave the remaining items for later
discussion.
Done.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com