Re: Fix bug of CHECK constraint enforceability recursion

> On May 26, 2026, at 14:05, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Tue, May 26, 2026 at 11:51 AM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>>
>> Hi,
>>
>> I just tested “Add support for altering CHECK constraint enforceability” and found an issue where recursion is not handled properly.
>>
>> Here is a repro with inheritance tables:
>> ```
>> evantest=# create table p(a int constraint ck check (a > 0) enforced);
>> CREATE TABLE
>> evantest=# create table c() inherits (p);
>> CREATE TABLE
>> evantest=# alter table c alter constraint ck not enforced;
>> ALTER TABLE
>> evantest=# insert into c values (-1);
>> INSERT 0 1
>> evantest=# alter table p alter constraint ck enforced;
>> ALTER TABLE
>> evantest=# insert into c values (-2);
>> INSERT 0 1
>> evantest=# 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:
>> ```
>
> Hi.
>
> 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))));
>
> 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.
> */
>
> 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.
>
> 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.
>
> 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.
>

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’m afraid it’s 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/