Fix bug of CHECK constraint enforceability recursion

From: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, jian he <jian(dot)universality(at)gmail(dot)com>
Subject: Fix bug of CHECK constraint enforceability recursion
Date: 2026-05-26 03:51:11
Message-ID: E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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:
```
evantest=# create table p (a int, constraint ck check (a > 0) enforced) partition by range (a);
CREATE TABLE
evantest=# create table p1 partition of p for values from (-100) to (100);
CREATE TABLE
evantest=# insert into p1 values (-1);
ERROR: new row for relation "p1" violates check constraint "ck"
DETAIL: Failing row contains (-1).
evantest=# alter table p1 alter constraint ck not enforced;
ALTER TABLE
evantest=# insert into p1 values (-1);
INSERT 0 1
evantest=# alter table p alter constraint ck enforced;
ALTER TABLE
evantest=# insert into p1 values (-2);
INSERT 0 1
evantest=#
evantest=# select * from p;
a
----
-1
-2
(2 rows)
```

For the solution, I think we should always recurse to descendant tables unless the constraint is NO INHERIT, because both partitioned tables and inheritance children can currently be altered to have different enforceability. So we cannot rely on whether the parent constraint itself was changed.

See the attached patch for details. I also added regress test cases for the fix.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachment Content-Type Size
v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch application/octet-stream 8.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2026-05-26 04:36:24 Re: [PATCH] Release replication slot on error in SQL-callable slot functions
Previous Message Zizhuan Liu 2026-05-26 03:42:46 Re: [PATCH] Little refactoring of portalcmds.c