public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: jian he <[email protected]>
Subject: Fix bug of CHECK constraint enforceability recursion
Date: Tue, 26 May 2026 11:51:11 +0800
Message-ID: <[email protected]> (raw)
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/
Attachments:
[application/octet-stream] v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch (8.3K, 2-v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch)
download | inline diff:
From 2755f0abcda9e0dfb38b715b7938ca50d9194920 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Tue, 26 May 2026 11:03:15 +0800
Subject: [PATCH v1] Fix CHECK constraint enforceability recursion
ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED skipped recursion when
the constraint on the target table was already enforced. That assumed
descendant CHECK constraints could not have different enforceability, but
both regular inheritance and some partition cases can have descendants with
different conenforced states.
Recurse for inheritable CHECK constraints regardless of whether the target
constraint row itself changed, while still skipping NO INHERIT
constraints. This ensures descendant constraints are updated and validated
when needed.
Add regression tests for both regular inheritance and partitioning: change a
descendant CHECK constraint to NOT ENFORCED while the parent remains ENFORCED,
then re-enforce the parent and verify that the operation recurses to the
descendant.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/
---
src/backend/commands/tablecmds.c | 15 ++++++-------
src/test/regress/expected/constraints.out | 26 +++++++++++++++++++++++
src/test/regress/expected/inherit.out | 24 +++++++++++++++++++++
src/test/regress/sql/constraints.sql | 18 ++++++++++++++++
src/test/regress/sql/inherit.sql | 15 +++++++++++++
5 files changed, 90 insertions(+), 8 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1e0bacf85fc..05289207305 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12700,14 +12700,13 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
}
/*
- * Note that we must recurse even when trying to change a check constraint
- * to not enforced if it is already not enforced, in case descendant
- * constraints might be enforced and need to be changed to not enforced.
- * Conversely, we should do nothing if a constraint is being set to
- * enforced and is already enforced, as descendant constraints cannot be
- * different in that case.
- */
- if (!cmdcon->is_enforced || changed)
+ * Recurse for inheritable constraints even when this constraint already
+ * has the requested enforceability. For both partitioning and regular
+ * inheritance, descendant constraints can have different enforceability
+ * and still need to be updated. Only NO INHERIT constraints do not
+ * recurse.
+ */
+ if (!currcon->connoinherit)
{
/*
* If we're recursing, the parent has already done this, so skip it.
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..579882e75f6 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -468,6 +468,32 @@ select * from check_constraint_status;
drop table parted_ch;
drop view check_constraint_status;
+-- an already enforced partitioned parent must still recurse to partitions
+create table parted_ch_recurse(
+ a int,
+ constraint cc check (a > 0) enforced
+) partition by range(a);
+create table parted_ch_recurse_1 partition of parted_ch_recurse for values from (-100) to (100);
+alter table parted_ch_recurse_1 alter constraint cc not enforced;
+insert into parted_ch_recurse_1 values(-1);
+alter table parted_ch_recurse alter constraint cc enforced; --error
+ERROR: check constraint "cc" of relation "parted_ch_recurse_1" is violated by some row
+delete from parted_ch_recurse_1 where a = -1;
+alter table parted_ch_recurse alter constraint cc enforced;
+select conrelid::regclass, conenforced, convalidated
+from pg_constraint
+where conname = 'cc' and conrelid::regclass::text like 'parted_ch_recurse%'
+order by conrelid::regclass::text;
+ conrelid | conenforced | convalidated
+---------------------+-------------+--------------
+ parted_ch_recurse | t | t
+ parted_ch_recurse_1 | t | t
+(2 rows)
+
+insert into parted_ch_recurse_1 values(-1); --error
+ERROR: new row for relation "parted_ch_recurse_1" violates check constraint "cc"
+DETAIL: Failing row contains (-1).
+drop table parted_ch_recurse;
--
-- Primary keys
--
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 3d8e8d8afd2..05a1604d609 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1479,6 +1479,30 @@ NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table p1_c1
drop cascades to table p1_c2
drop cascades to table p1_c3
+-- an already enforced parent must still recurse to regular inheritance children
+create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
+create table p1_c1() inherits(p1);
+alter table p1_c1 alter constraint p1_a_check not enforced;
+insert into p1_c1 values(-1);
+alter table p1 alter constraint p1_a_check enforced; --error
+ERROR: check constraint "p1_a_check" of relation "p1_c1" is violated by some row
+delete from p1_c1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+ conname | conenforced | convalidated | conrelid
+------------+-------------+--------------+----------
+ p1_a_check | t | t | p1
+ p1_a_check | t | t | p1_c1
+(2 rows)
+
+insert into p1_c1 values(-1); --error
+ERROR: new row for relation "p1_c1" violates check constraint "p1_a_check"
+DETAIL: Failing row contains (-1).
+drop table p1 cascade;
+NOTICE: drop cascades to table p1_c1
--for "no inherit" check constraint, it will not recurse to child table
create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index dc133b124bb..c84eaa44b9c 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -318,6 +318,24 @@ select * from check_constraint_status;
drop table parted_ch;
drop view check_constraint_status;
+-- an already enforced partitioned parent must still recurse to partitions
+create table parted_ch_recurse(
+ a int,
+ constraint cc check (a > 0) enforced
+) partition by range(a);
+create table parted_ch_recurse_1 partition of parted_ch_recurse for values from (-100) to (100);
+alter table parted_ch_recurse_1 alter constraint cc not enforced;
+insert into parted_ch_recurse_1 values(-1);
+alter table parted_ch_recurse alter constraint cc enforced; --error
+delete from parted_ch_recurse_1 where a = -1;
+alter table parted_ch_recurse alter constraint cc enforced;
+select conrelid::regclass, conenforced, convalidated
+from pg_constraint
+where conname = 'cc' and conrelid::regclass::text like 'parted_ch_recurse%'
+order by conrelid::regclass::text;
+insert into parted_ch_recurse_1 values(-1); --error
+drop table parted_ch_recurse;
+
--
-- Primary keys
--
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 8f986904389..8a7417765aa 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -535,6 +535,21 @@ where conname = 'inh_check_constraint3' and contype = 'c'
order by conrelid::regclass::text collate "C";
drop table p1 cascade;
+-- an already enforced parent must still recurse to regular inheritance children
+create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
+create table p1_c1() inherits(p1);
+alter table p1_c1 alter constraint p1_a_check not enforced;
+insert into p1_c1 values(-1);
+alter table p1 alter constraint p1_a_check enforced; --error
+delete from p1_c1;
+alter table p1 alter constraint p1_a_check enforced; --ok
+select conname, conenforced, convalidated, conrelid::regclass
+from pg_constraint
+where conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+insert into p1_c1 values(-1); --error
+drop table p1 cascade;
+
--for "no inherit" check constraint, it will not recurse to child table
create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
--
2.50.1 (Apple Git-155)
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