MIME-Version: 1.0
References: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
In-Reply-To: <E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 26 May 2026 14:05:08 +0800
Message-ID: 
 <CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
To: Chao Li <li.evan.chao@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Andrew Dunstan <andrew@dunslane.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxGRkCSqqRwd%2B3MMVFbosKXHv%3DJc%2BKyGK%2BNM3YAHN7eu%2Bg%40mail.gmail.com>
Precedence: bulk

On Tue, May 26, 2026 at 11:51=E2=80=AFAM Chao Li <li.evan.chao@gmail.com> w=
rote:
>
> Hi,
>
> I just tested =E2=80=9CAdd support for altering CHECK constraint enforcea=
bility=E2=80=9D and found an issue where recursion is not handled properly.
>
> 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)
> ```
>
> 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 alteri=
ng 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 par=
ent
         * 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 expec=
t a
state where the parent constraint is enforced but the child constraint is n=
ot.
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 constr=
aint
is not enforced.


--
jian
https://www.enterprisedb.com/