Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Re: Fix bug of CHECK constraint enforceability recursion
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
Date: Tue, 26 May 2026 14:27:34 +0800
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Andrew Dunstan <andrew@dunslane.net>
Content-Transfer-Encoding: quoted-printable
Message-Id: <DCE683F9-CEBF-4EDE-81D1-5E59CCD5CF82@gmail.com>
References: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
 <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
To: jian he <jian.universality@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DCE683F9-CEBF-4EDE-81D1-5E59CCD5CF82%40gmail.com>
Precedence: bulk


> On May 26, 2026, at 14:05, jian he <jian.universality@gmail.com> =
wrote:
>=20
> On Tue, May 26, 2026 at 11:51=E2=80=AFAM Chao Li =
<li.evan.chao@gmail.com> wrote:
>>=20
>> Hi,
>>=20
>> 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.
>>=20
>> 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)
>> ```
>>=20
>> 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.
>>=20
>> The same problem can happen with partitioned tables as well:
>> ```
>=20
> Hi.
>=20
> In MergeConstraintsIntoExisting, we have:
> /*
> * A NOT ENFORCED child constraint cannot be merged with an
> * ENFORCED parent constraint. However, the reverse is allowed,
> * where the child constraint is ENFORCED.
> */
> if (parent_con->conenforced && !child_con->conenforced)
>    ereport(ERROR,
>            (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
>             errmsg("constraint \"%s\" conflicts with NOT ENFORCED
> constraint on child table \"%s\"",
>                NameStr(child_con->conname),
> RelationGetRelationName(child_rel))));
>=20
> MergeWithExistingConstraint, we have comments like:
>        /*
>         * If the child constraint is required to be enforced while the =
parent
>         * constraint is not, this should be allowed by marking the =
child
>         * constraint as enforced. In the reverse case, an error would =
have
>         * already been thrown before reaching this point.
>         */
>=20
> So other commands (CREATE TABLE, ALTER TABLE ATTACH PARTITION) do not =
expect a
> state where the parent constraint is enforced but the child constraint =
is not.
> We can now reach this state via ALTER TABLE ALTER CONSTRAINT.
>=20
> We don't need to worry about Foreign Key Constraints because the
> foreign key constraint's conparentid is valid, therefore we cannot
> directly alter a partition's FK constraint.
> StoreRelCheck->CreateConstraintEntry comments ``/* no parent
> constraint */`` means that each CHECK constraint is on its own.
>=20
> Overall, i tend to think that we should reject ALTER TABLE ALTER
> CONSTRAINT if it
> would result in the parent constraint being enforced while the child =
constraint
> is not enforced.
>=20

I am not against the idea of "rejecting ALTER TABLE ALTER CONSTRAINT if =
it would result in the parent constraint being enforced while the child =
constrain is not enforced", but I=E2=80=99m afraid it=E2=80=99s too late =
for PG19. So, I guess we still need to fix the issue for 19, right?

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