public inbox for [email protected]  

help / color / mirror / Atom feed

From: Chao Li <[email protected]>
To: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
Date: Tue, 26 May 2026 15:44:01 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
	<[email protected]>



> 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/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Fix bug of CHECK constraint enforceability recursion
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox