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/
