MIME-Version: 1.0
References: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
 <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
 <DCE683F9-CEBF-4EDE-81D1-5E59CCD5CF82@gmail.com>
 <cb4a41fd-67e8-47ed-bcff-d0549dce3f02@app.fastmail.com>
 <33E9C4C2-B6A8-4FCC-BEEA-461EA5FB98C8@gmail.com>
 <CACJufxGxY=1mxcNz8-bjp6R+ZoCRE3O7DYt2ZqObv-Le+aMOqw@mail.gmail.com>
 <7F0EA98A-6DBC-436A-8FF4-4A511A05ABE6@gmail.com>
In-Reply-To: <7F0EA98A-6DBC-436A-8FF4-4A511A05ABE6@gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Thu, 28 May 2026 10:31:35 +0800
Message-ID: 
 <CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ@mail.gmail.com>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
To: Chao Li <li.evan.chao@gmail.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
	"L. pgsql-hackers" <pgsql-hackers@lists.postgresql.org>,
 Andrew Dunstan <andrew@dunslane.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ%40mail.gmail.com>
Precedence: bulk

On Wed, May 27, 2026 at 2:20=E2=80=AFPM Chao Li <li.evan.chao@gmail.com> wr=
ote:
>
> 4. It cannot handle some complicated inheritance hierarchies. For example=
, the following test passes with your v1:
> ```
> evantest=3D# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=3D# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=3D#
> evantest=3D# CREATE TABLE ch () INHERITS (p1, p2);
> NOTICE:  merging multiple inherited definitions of column "a"
> CREATE TABLE
> evantest=3D# ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
> ALTER TABLE
> ```
>
> I originally thought this should fail, but it now changes ch.c to NOT ENF=
ORCED, so it breaks the rule because its parent p2 is still ENFORCED:
> ```
> evantest=3D# SELECT conrelid::regclass, conname, conenforced, coninhcount=
, conislocal
> evantest-# FROM pg_constraint WHERE conname =3D 'c';
>  conrelid | conname | conenforced | coninhcount | conislocal
> ----------+---------+-------------+-------------+------------
>  p1       | c       | f           |           0 | t
>  p2       | c       | t           |           0 | t
>  ch       | c       | f           |           2 | f
> (3 rows)
> ```
>
> Then I realized that the initial CREATE TABLE case passes:
> ```
> evantest=3D# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) NOT ENFORC=
ED);
> CREATE TABLE
> evantest=3D# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=3D# CREATE TABLE ch () INHERITS (p1, p2);
> NOTICE:  merging multiple inherited definitions of column "a"
> CREATE TABLE
> evantest=3D# SELECT conrelid::regclass, conname, conenforced, coninhcount=
, conislocal
> evantest-# FROM pg_constraint WHERE conname =3D =E2=80=98c';
>  conrelid | conname | conenforced | coninhcount | conislocal
> ----------+---------+-------------+-------------+------------
>  ch       | c       | t           |           2 | f
>  p1       | c       | f           |           0 | t
>  p2       | c       | t           |           0 | t
> (3 rows)
> ```
>
> When the two parents have different enforceability, the stricter one is a=
pplied to the child. So I think the test above in item 4 should also perfor=
m similar merge logic rather than fail. This seems to uncover a new issue i=
n the original feature patch.
>

> For the fix, my design is:
>
> * Directly reject changing an inherited child CHECK constraint to NOT ENF=
ORCED if an equivalent parent constraint remains ENFORCED.
> * Changing a child to ENFORCED is allowed.
> * During recursing, if a child also inherits an equivalent ENFORCED const=
raint from another parent outside the current ALTER, the child keeps the st=
ricter ENFORCED state.
>
> Please see my implementation in the attached v2 patch.

CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE ch () INHERITS (p1, p2);
ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;

The v2 patch marks check constraint c on table ch as ENFORCED, which
seems to contradict the documentation's wording:
https://www.postgresql.org/docs/devel/ddl-inherit.html
<<>>
ALTER TABLE will propagate any changes in column data definitions and check
constraints down the inheritance hierarchy. Again, dropping columns that ar=
e
depended on by other tables is only possible when using the CASCADE option.
ALTER TABLE follows the same rules for duplicate column merging and rejecti=
on
that apply during CREATE TABLE
<<>>


The wording (https://www.postgresql.org/docs/devel/ddl-inherit.html)
below also discourages directly altering check constraints on child tables.
<<>>
A parent table cannot be dropped while any of its children remain. Neither =
can
columns or check constraints of child tables be dropped or altered if they =
are
inherited from any parent tables. If you wish to remove a table and all of =
its
descendants, one easy way is to drop the parent table with the CASCADE opti=
on
(see Section 5.17).
<<>>


--
jian
https://www.enterprisedb.com/