public inbox for [email protected]  

help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Chao Li <[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 14:05:08 +0800
Message-ID: <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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.



--
jian
https://www.enterprisedb.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: <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>

* 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