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: Re: Fix bug of CHECK constraint enforceability recursion
Date: Tue, 26 May 2026 13:48:02 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
> On May 26, 2026, at 11:51, 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:
> ```
> 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/
>
>
>
>
> <v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch>
Merged the doc change from [1] into this thread as they are for the same feature.
[1] https://postgr.es/m/[email protected]
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v2-0001-Fix-CHECK-constraint-enforceability-recursion.patch (8.4K, 2-v2-0001-Fix-CHECK-constraint-enforceability-recursion.patch)
download | inline diff:
From c28ecbe5ac850fb77c9f0b859c7d6a2b867cc4b9 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Tue, 26 May 2026 11:03:15 +0800
Subject: [PATCH v2 1/2] 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/[email protected]
---
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)
[application/octet-stream] v2-0002-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch (2.2K, 3-v2-0002-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch)
download | inline diff:
From 060abd87eddc336fe4a73c980f095aeda6522b3c Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 25 May 2026 16:59:21 +0800
Subject: [PATCH v2 2/2] doc: Clarify ALTER CONSTRAINT enforceability behavior
The ALTER TABLE documentation said that FOREIGN KEY and CHECK
constraints may be altered, but did not distinguish between
deferrability and enforceability attributes.
Clarify that deferrability attributes can currently be altered only for
FOREIGN KEY constraints, while enforceability can be altered for both
FOREIGN KEY and CHECK constraints. Also document that setting a
constraint to ENFORCED verifies existing rows and resumes checking new
or updated rows.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ref/alter_table.sgml | 14 ++++++++++++--
1 file changed, 12 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dec34337d1a..af247d82902 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -586,8 +586,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently <literal>FOREIGN KEY</literal> and <literal>CHECK</literal>
- constraints may be altered in this fashion, but see below.
+ created. Currently, the deferrability attributes can be altered only
+ for <literal>FOREIGN KEY</literal> constraints. The enforceability
+ attribute can be altered for <literal>FOREIGN KEY</literal> and
+ <literal>CHECK</literal> constraints.
+ </para>
+
+ <para>
+ Setting a constraint to <literal>NOT ENFORCED</literal> causes the
+ database system to stop checking it for new or updated rows. Setting
+ a constraint to <literal>ENFORCED</literal> causes the database system
+ to verify that existing rows satisfy the constraint and to check it
+ for new or updated rows.
</para>
</listitem>
</varlistentry>
--
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