From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_4E78BCB4-08FD-45B6-B7CF-5FA226CAF531"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Fix bug of CHECK constraint enforceability recursion
Message-Id: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
Date: Tue, 26 May 2026 11:51:11 +0800
Cc: Andrew Dunstan <andrew@dunslane.net>,
 jian he <jian.universality@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/E74C57FA-1DD0-4C8E-8FB1-538034752592%40gmail.com>
Precedence: bulk


--Apple-Mail=_4E78BCB4-08FD-45B6-B7CF-5FA226CAF531
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hi,

I just tested =E2=80=9CAdd support for altering CHECK constraint =
enforceability=E2=80=9D and found an issue where recursion is not =
handled properly.

Here is a repro with inheritance tables:
```
evantest=3D# create table p(a int constraint ck check (a > 0) enforced);
CREATE TABLE
evantest=3D# create table c() inherits (p);
CREATE TABLE
evantest=3D# alter table c alter constraint ck not enforced;
ALTER TABLE
evantest=3D# insert into c values (-1);
INSERT 0 1
evantest=3D# alter table p alter constraint ck enforced;
ALTER TABLE
evantest=3D# insert into c values (-2);
INSERT 0 1
evantest=3D# select * from p;
 a
----
 -1
 -2
(2 rows)
```

In this repro, the constraint on parent table p is already ENFORCED, but =
the constraint on child table c was altered to NOT ENFORCED. So when =
altering p to ENFORCED again, it didn't recurse to c.

The same problem can happen with partitioned tables as well:
```
evantest=3D# create table p (a int, constraint ck check (a > 0) =
enforced) partition by range (a);
CREATE TABLE
evantest=3D# create table p1 partition of p for values from (-100) to =
(100);
CREATE TABLE
evantest=3D# insert into p1 values (-1);
ERROR:  new row for relation "p1" violates check constraint "ck"
DETAIL:  Failing row contains (-1).
evantest=3D# alter table p1 alter constraint ck not enforced;
ALTER TABLE
evantest=3D# insert into p1 values (-1);
INSERT 0 1
evantest=3D# alter table p alter constraint ck enforced;
ALTER TABLE
evantest=3D# insert into p1 values (-2);
INSERT 0 1
evantest=3D#
evantest=3D# select * from p;
 a
----
 -1
 -2
(2 rows)
```

For the solution, I think we should always recurse to descendant tables =
unless the constraint is NO INHERIT, because both partitioned tables and =
inheritance children can currently be altered to have different =
enforceability. So we cannot rely on whether the parent constraint =
itself was changed.

See the attached patch for details. I also added regress test cases for =
the fix.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_4E78BCB4-08FD-45B6-B7CF-5FA226CAF531
Content-Disposition: attachment;
	filename=v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=202755f0abcda9e0dfb38b715b7938ca50d9194920=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Tue,=2026=20May=202026=2011:03:15=20+0800=0ASubject:=20[PATCH=20=
v1]=20Fix=20CHECK=20constraint=20enforceability=20recursion=0A=0AALTER=20=
TABLE=20...=20ALTER=20CONSTRAINT=20...=20ENFORCED=20skipped=20recursion=20=
when=0Athe=20constraint=20on=20the=20target=20table=20was=20already=20=
enforced.=20That=20assumed=0Adescendant=20CHECK=20constraints=20could=20=
not=20have=20different=20enforceability,=20but=0Aboth=20regular=20=
inheritance=20and=20some=20partition=20cases=20can=20have=20descendants=20=
with=0Adifferent=20conenforced=20states.=0A=0ARecurse=20for=20=
inheritable=20CHECK=20constraints=20regardless=20of=20whether=20the=20=
target=0Aconstraint=20row=20itself=20changed,=20while=20still=20skipping=20=
NO=20INHERIT=0Aconstraints.=20This=20ensures=20descendant=20constraints=20=
are=20updated=20and=20validated=0Awhen=20needed.=0A=0AAdd=20regression=20=
tests=20for=20both=20regular=20inheritance=20and=20partitioning:=20=
change=20a=0Adescendant=20CHECK=20constraint=20to=20NOT=20ENFORCED=20=
while=20the=20parent=20remains=20ENFORCED,=0Athen=20re-enforce=20the=20=
parent=20and=20verify=20that=20the=20operation=20recurses=20to=20the=0A=
descendant.=0A=0AAuthor:=20Chao=20Li=20<lic@highgo.com>=0AReviewed-by:=0A=
Discussion:=20https://postgr.es/m/=0A---=0A=20=
src/backend/commands/tablecmds.c=20=20=20=20=20=20=20=20=20=20|=2015=20=
++++++-------=0A=20src/test/regress/expected/constraints.out=20|=2026=20=
+++++++++++++++++++++++=0A=20src/test/regress/expected/inherit.out=20=20=20=
=20=20|=2024=20+++++++++++++++++++++=0A=20=
src/test/regress/sql/constraints.sql=20=20=20=20=20=20|=2018=20=
++++++++++++++++=0A=20src/test/regress/sql/inherit.sql=20=20=20=20=20=20=20=
=20=20=20|=2015=20+++++++++++++=0A=205=20files=20changed,=2090=20=
insertions(+),=208=20deletions(-)=0A=0Adiff=20--git=20=
a/src/backend/commands/tablecmds.c=20b/src/backend/commands/tablecmds.c=0A=
index=201e0bacf85fc..05289207305=20100644=0A---=20=
a/src/backend/commands/tablecmds.c=0A+++=20=
b/src/backend/commands/tablecmds.c=0A@@=20-12700,14=20+12700,13=20@@=20=
ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20=
ATAlterConstraint=20*cmdcon,=0A=20=09}=0A=20=0A=20=09/*=0A-=09=20*=20=
Note=20that=20we=20must=20recurse=20even=20when=20trying=20to=20change=20=
a=20check=20constraint=0A-=09=20*=20to=20not=20enforced=20if=20it=20is=20=
already=20not=20enforced,=20in=20case=20descendant=0A-=09=20*=20=
constraints=20might=20be=20enforced=20and=20need=20to=20be=20changed=20=
to=20not=20enforced.=0A-=09=20*=20Conversely,=20we=20should=20do=20=
nothing=20if=20a=20constraint=20is=20being=20set=20to=0A-=09=20*=20=
enforced=20and=20is=20already=20enforced,=20as=20descendant=20=
constraints=20cannot=20be=0A-=09=20*=20different=20in=20that=20case.=0A-=09=
=20*/=0A-=09if=20(!cmdcon->is_enforced=20||=20changed)=0A+=09=20*=20=
Recurse=20for=20inheritable=20constraints=20even=20when=20this=20=
constraint=20already=0A+=09=20*=20has=20the=20requested=20=
enforceability.=20=20For=20both=20partitioning=20and=20regular=0A+=09=20=
*=20inheritance,=20descendant=20constraints=20can=20have=20different=20=
enforceability=0A+=09=20*=20and=20still=20need=20to=20be=20updated.=20=20=
Only=20NO=20INHERIT=20constraints=20do=20not=0A+=09=20*=20recurse.=0A+=09=
=20*/=0A+=09if=20(!currcon->connoinherit)=0A=20=09{=0A=20=09=09/*=0A=20=09=
=09=20*=20If=20we're=20recursing,=20the=20parent=20has=20already=20done=20=
this,=20so=20skip=20it.=0Adiff=20--git=20=
a/src/test/regress/expected/constraints.out=20=
b/src/test/regress/expected/constraints.out=0Aindex=20=
e54fec7fb57..579882e75f6=20100644=0A---=20=
a/src/test/regress/expected/constraints.out=0A+++=20=
b/src/test/regress/expected/constraints.out=0A@@=20-468,6=20+468,32=20@@=20=
select=20*=20from=20check_constraint_status;=0A=20=0A=20drop=20table=20=
parted_ch;=0A=20drop=20view=20check_constraint_status;=0A+--=20an=20=
already=20enforced=20partitioned=20parent=20must=20still=20recurse=20to=20=
partitions=0A+create=20table=20parted_ch_recurse(=0A+=20=20a=20int,=0A+=20=
=20constraint=20cc=20check=20(a=20>=200)=20enforced=0A+)=20partition=20=
by=20range(a);=0A+create=20table=20parted_ch_recurse_1=20partition=20of=20=
parted_ch_recurse=20for=20values=20from=20(-100)=20to=20(100);=0A+alter=20=
table=20parted_ch_recurse_1=20alter=20constraint=20cc=20not=20enforced;=0A=
+insert=20into=20parted_ch_recurse_1=20values(-1);=0A+alter=20table=20=
parted_ch_recurse=20alter=20constraint=20cc=20enforced;=20--error=0A=
+ERROR:=20=20check=20constraint=20"cc"=20of=20relation=20=
"parted_ch_recurse_1"=20is=20violated=20by=20some=20row=0A+delete=20from=20=
parted_ch_recurse_1=20where=20a=20=3D=20-1;=0A+alter=20table=20=
parted_ch_recurse=20alter=20constraint=20cc=20enforced;=0A+select=20=
conrelid::regclass,=20conenforced,=20convalidated=0A+from=20=
pg_constraint=0A+where=20conname=20=3D=20'cc'=20and=20=
conrelid::regclass::text=20like=20'parted_ch_recurse%'=0A+order=20by=20=
conrelid::regclass::text;=0A+=20=20=20=20=20=20conrelid=20=20=20=20=20=20=
=20|=20conenforced=20|=20convalidated=20=0A=
+---------------------+-------------+--------------=0A+=20=
parted_ch_recurse=20=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A=
+=20parted_ch_recurse_1=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A=
+(2=20rows)=0A+=0A+insert=20into=20parted_ch_recurse_1=20values(-1);=20=
--error=0A+ERROR:=20=20new=20row=20for=20relation=20=
"parted_ch_recurse_1"=20violates=20check=20constraint=20"cc"=0A+DETAIL:=20=
=20Failing=20row=20contains=20(-1).=0A+drop=20table=20parted_ch_recurse;=0A=
=20--=0A=20--=20Primary=20keys=0A=20--=0Adiff=20--git=20=
a/src/test/regress/expected/inherit.out=20=
b/src/test/regress/expected/inherit.out=0Aindex=20=
3d8e8d8afd2..05a1604d609=20100644=0A---=20=
a/src/test/regress/expected/inherit.out=0A+++=20=
b/src/test/regress/expected/inherit.out=0A@@=20-1479,6=20+1479,30=20@@=20=
NOTICE:=20=20drop=20cascades=20to=203=20other=20objects=0A=20DETAIL:=20=20=
drop=20cascades=20to=20table=20p1_c1=0A=20drop=20cascades=20to=20table=20=
p1_c2=0A=20drop=20cascades=20to=20table=20p1_c3=0A+--=20an=20already=20=
enforced=20parent=20must=20still=20recurse=20to=20regular=20inheritance=20=
children=0A+create=20table=20p1(f1=20int=20constraint=20p1_a_check=20=
check=20(f1=20>=200)=20enforced);=0A+create=20table=20p1_c1()=20=
inherits(p1);=0A+alter=20table=20p1_c1=20alter=20constraint=20p1_a_check=20=
not=20enforced;=0A+insert=20into=20p1_c1=20values(-1);=0A+alter=20table=20=
p1=20alter=20constraint=20p1_a_check=20enforced;=20--error=0A+ERROR:=20=20=
check=20constraint=20"p1_a_check"=20of=20relation=20"p1_c1"=20is=20=
violated=20by=20some=20row=0A+delete=20from=20p1_c1;=0A+alter=20table=20=
p1=20alter=20constraint=20p1_a_check=20enforced;=20--ok=0A+select=20=20=
conname,=20conenforced,=20convalidated,=20conrelid::regclass=0A+from=20=20=
=20=20pg_constraint=0A+where=20=20=20conname=20=3D=20'p1_a_check'=20and=20=
contype=20=3D=20'c'=0A+order=20by=20conrelid::regclass::text=20collate=20=
"C";=0A+=20=20conname=20=20=20|=20conenforced=20|=20convalidated=20|=20=
conrelid=20=0A+------------+-------------+--------------+----------=0A+=20=
p1_a_check=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=20=20=20=20=20=20=
=20=20=20=20=20=20|=20p1=0A+=20p1_a_check=20|=20t=20=20=20=20=20=20=20=20=
=20=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20=20|=20p1_c1=0A+(2=20=
rows)=0A+=0A+insert=20into=20p1_c1=20values(-1);=20--error=0A+ERROR:=20=20=
new=20row=20for=20relation=20"p1_c1"=20violates=20check=20constraint=20=
"p1_a_check"=0A+DETAIL:=20=20Failing=20row=20contains=20(-1).=0A+drop=20=
table=20p1=20cascade;=0A+NOTICE:=20=20drop=20cascades=20to=20table=20=
p1_c1=0A=20--for=20"no=20inherit"=20check=20constraint,=20it=20will=20=
not=20recurse=20to=20child=20table=0A=20create=20table=20p1(f1=20int=20=
constraint=20p1_a_check=20check=20(f1=20>=200)=20no=20inherit=20not=20=
enforced);=0A=20create=20table=20p1_c1(f1=20int=20constraint=20=
p1_a_check=20check=20(f1=20>=200)=20not=20enforced);=0Adiff=20--git=20=
a/src/test/regress/sql/constraints.sql=20=
b/src/test/regress/sql/constraints.sql=0Aindex=20=
dc133b124bb..c84eaa44b9c=20100644=0A---=20=
a/src/test/regress/sql/constraints.sql=0A+++=20=
b/src/test/regress/sql/constraints.sql=0A@@=20-318,6=20+318,24=20@@=20=
select=20*=20from=20check_constraint_status;=0A=20drop=20table=20=
parted_ch;=0A=20drop=20view=20check_constraint_status;=0A=20=0A+--=20an=20=
already=20enforced=20partitioned=20parent=20must=20still=20recurse=20to=20=
partitions=0A+create=20table=20parted_ch_recurse(=0A+=20=20a=20int,=0A+=20=
=20constraint=20cc=20check=20(a=20>=200)=20enforced=0A+)=20partition=20=
by=20range(a);=0A+create=20table=20parted_ch_recurse_1=20partition=20of=20=
parted_ch_recurse=20for=20values=20from=20(-100)=20to=20(100);=0A+alter=20=
table=20parted_ch_recurse_1=20alter=20constraint=20cc=20not=20enforced;=0A=
+insert=20into=20parted_ch_recurse_1=20values(-1);=0A+alter=20table=20=
parted_ch_recurse=20alter=20constraint=20cc=20enforced;=20--error=0A=
+delete=20from=20parted_ch_recurse_1=20where=20a=20=3D=20-1;=0A+alter=20=
table=20parted_ch_recurse=20alter=20constraint=20cc=20enforced;=0A=
+select=20conrelid::regclass,=20conenforced,=20convalidated=0A+from=20=
pg_constraint=0A+where=20conname=20=3D=20'cc'=20and=20=
conrelid::regclass::text=20like=20'parted_ch_recurse%'=0A+order=20by=20=
conrelid::regclass::text;=0A+insert=20into=20parted_ch_recurse_1=20=
values(-1);=20--error=0A+drop=20table=20parted_ch_recurse;=0A+=0A=20--=0A=
=20--=20Primary=20keys=0A=20--=0Adiff=20--git=20=
a/src/test/regress/sql/inherit.sql=20b/src/test/regress/sql/inherit.sql=0A=
index=208f986904389..8a7417765aa=20100644=0A---=20=
a/src/test/regress/sql/inherit.sql=0A+++=20=
b/src/test/regress/sql/inherit.sql=0A@@=20-535,6=20+535,21=20@@=20where=20=
=20=20conname=20=3D=20'inh_check_constraint3'=20and=20contype=20=3D=20=
'c'=0A=20order=20by=20conrelid::regclass::text=20collate=20"C";=0A=20=
drop=20table=20p1=20cascade;=0A=20=0A+--=20an=20already=20enforced=20=
parent=20must=20still=20recurse=20to=20regular=20inheritance=20children=0A=
+create=20table=20p1(f1=20int=20constraint=20p1_a_check=20check=20(f1=20=
>=200)=20enforced);=0A+create=20table=20p1_c1()=20inherits(p1);=0A+alter=20=
table=20p1_c1=20alter=20constraint=20p1_a_check=20not=20enforced;=0A=
+insert=20into=20p1_c1=20values(-1);=0A+alter=20table=20p1=20alter=20=
constraint=20p1_a_check=20enforced;=20--error=0A+delete=20from=20p1_c1;=0A=
+alter=20table=20p1=20alter=20constraint=20p1_a_check=20enforced;=20--ok=0A=
+select=20=20conname,=20conenforced,=20convalidated,=20=
conrelid::regclass=0A+from=20=20=20=20pg_constraint=0A+where=20=20=20=
conname=20=3D=20'p1_a_check'=20and=20contype=20=3D=20'c'=0A+order=20by=20=
conrelid::regclass::text=20collate=20"C";=0A+insert=20into=20p1_c1=20=
values(-1);=20--error=0A+drop=20table=20p1=20cascade;=0A+=0A=20--for=20=
"no=20inherit"=20check=20constraint,=20it=20will=20not=20recurse=20to=20=
child=20table=0A=20create=20table=20p1(f1=20int=20constraint=20=
p1_a_check=20check=20(f1=20>=200)=20no=20inherit=20not=20enforced);=0A=20=
create=20table=20p1_c1(f1=20int=20constraint=20p1_a_check=20check=20(f1=20=
>=200)=20not=20enforced);=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A=

--Apple-Mail=_4E78BCB4-08FD-45B6-B7CF-5FA226CAF531--