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: <DCE683F9-CEBF-4EDE-81D1-5E59CCD5CF82@gmail.com>
Date: Tue, 26 May 2026 15:44:01 +0800
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Andrew Dunstan <andrew@dunslane.net>
Content-Transfer-Encoding: quoted-printable
Message-Id: <E367DFE2-FC45-4D0F-9988-3FEB89A8C6C8@gmail.com>
References: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
 <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
 <DCE683F9-CEBF-4EDE-81D1-5E59CCD5CF82@gmail.com>
To: jian he <jian.universality@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/E367DFE2-FC45-4D0F-9988-3FEB89A8C6C8%40gmail.com>
Precedence: bulk


> On May 26, 2026, at 14:27, Chao Li <li.evan.chao@gmail.com> wrote:
>=20
>=20
>=20
>> 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
>=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?
>=20

I thought this over, and I changed my mind.

The same rule should apply to both partitioned tables and regular =
inheritance:

* parent CHECK enforced + child CHECK not enforced =3D reject
* parent CHECK not enforced + child CHECK enforced =3D allow

That matches the existing merge/attach behavior. Also, this invariant =
could not be broken through normal SQL in PG18, because PG18 does not =
support ALTER TABLE ... ALTER CONSTRAINT ... [NOT] ENFORCED for CHECK =
constraints. So we should not introduce a new way to break it in PG19.

I will rework the patch forwards the =E2=80=9Creject=E2=80=9D direction.

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