> On May 26, 2026, at 14:27, Chao Li <[email protected]> wrote:
>
>
>
>> On May 26, 2026, at 14:05, jian he <[email protected]> wrote:
>>
>> On Tue, May 26, 2026 at 11:51 AM Chao Li <[email protected]> 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?
>
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 = reject
* parent CHECK not enforced + child CHECK enforced = 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 “reject” direction.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
