pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu
3+ messages / 3 participants
[nested] [flat]
* BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu
@ 2026-10-29 06:17 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-10-29 06:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
The following bug has been logged on the website:
Bug reference: 19807
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:
A CHECK constraint declared on a partitioned table is enforced at INSERT
time using the *partition's* version of a virtual generated column when the
partition overrides that column's expression with WITH OPTIONS. Queries
through the parent table, however, evaluate the column with the parent's
expression. As a result, a row that violates the parent's CHECK constraint
is accepted on INSERT and remains visible through the parent.
Test case (self-contained):
CREATE TABLE gp (f2 int,
f3 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL,
CONSTRAINT gp_ck CHECK (f3 > 30))
PARTITION BY RANGE (f2);
CREATE TABLE gp1 PARTITION OF gp FOR VALUES FROM (0) TO (8);
CREATE TABLE gp2 PARTITION OF gp
(f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL)
FOR VALUES FROM (8) TO (100);
INSERT INTO gp (f2) VALUES (10); -- accepted
Result on 18.6 (official docker image postgres:latest):
SELECT f2, f3 FROM gp; -> (10, 20) parent evaluates f3
= f2*2
SELECT f2, f3 FROM gp WHERE NOT (f3 > 30); -> (10, 20) a row visible
through the parent violates the parent's own CHECK
SELECT f2, f3 FROM gp2; -> (10, 220) the partition
evaluated f3 = f2*22
Baselines - the same constraint rejects the same INSERT as soon as no
expression override is involved:
(1) plain table, same CHECK:
ERROR: new row for relation "plain_t" violates check constraint "plain_ck"
(2) partitioned table without the WITH OPTIONS override:
ERROR: new row for relation "gb2" violates check constraint "gb_ck"
(3) partitioned table with the override: INSERT 0 1
Expected: the INSERT should be rejected, as in both baselines - or the
combination of a parent CHECK referencing a generated column together with a
partition expression override should be rejected at DDL time. A CHECK
constraint defined on the parent must hold for every row visible through the
parent.
Mechanism: constraint checking runs on the leaf partition using the leaf's
generated expression (f2*22 = 220 > 30 passes), while reads through the
parent compute the column with the parent's expression (f2*2 = 20). The two
evaluations disagree, so the constraint the user declared is never applied
to the value the user sees.
Related, same parent/leaf evaluation mismatch: a row-level trigger declared
on the parent with WHEN (OLD.f3 > 30) also fires based on the leaf
expression, i.e. for rows whose parent-visible value is 20.
Notes:
- The WITH OPTIONS clause is the documented syntax for overriding a
generated column's expression in a partition, so this combination is
reachable by ordinary DDL.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu
@ 2026-11-09 10:49 Dilip Kumar <dilipbalaut@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Dilip Kumar @ 2026-11-09 10:49 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
On Fri, Oct 30, 2026 at 12:42 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19807
> Logged by: chunling qin
> Email address: 303677365@qq.com
> PostgreSQL version: 18.6
> Operating system: Linux x86_64
> Description:
>
> A CHECK constraint declared on a partitioned table is enforced at INSERT
> time using the *partition's* version of a virtual generated column when the
> partition overrides that column's expression with WITH OPTIONS. Queries
> through the parent table, however, evaluate the column with the parent's
> expression. As a result, a row that violates the parent's CHECK constraint
> is accepted on INSERT and remains visible through the parent.
>
> Test case (self-contained):
>
> CREATE TABLE gp (f2 int,
> f3 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL,
> CONSTRAINT gp_ck CHECK (f3 > 30))
> PARTITION BY RANGE (f2);
> CREATE TABLE gp1 PARTITION OF gp FOR VALUES FROM (0) TO (8);
> CREATE TABLE gp2 PARTITION OF gp
> (f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL)
> FOR VALUES FROM (8) TO (100);
>
> INSERT INTO gp (f2) VALUES (10); -- accepted
>
> Result on 18.6 (official docker image postgres:latest):
>
> SELECT f2, f3 FROM gp; -> (10, 20) parent evaluates f3
> = f2*2
> SELECT f2, f3 FROM gp WHERE NOT (f3 > 30); -> (10, 20) a row visible
> through the parent violates the parent's own CHECK
> SELECT f2, f3 FROM gp2; -> (10, 220) the partition
> evaluated f3 = f2*22
>
> Baselines - the same constraint rejects the same INSERT as soon as no
> expression override is involved:
>
> (1) plain table, same CHECK:
> ERROR: new row for relation "plain_t" violates check constraint "plain_ck"
> (2) partitioned table without the WITH OPTIONS override:
> ERROR: new row for relation "gb2" violates check constraint "gb_ck"
> (3) partitioned table with the override: INSERT 0 1
>
> Expected: the INSERT should be rejected, as in both baselines - or the
> combination of a parent CHECK referencing a generated column together with a
> partition expression override should be rejected at DDL time. A CHECK
> constraint defined on the parent must hold for every row visible through the
> parent.
>
> Mechanism: constraint checking runs on the leaf partition using the leaf's
> generated expression (f2*22 = 220 > 30 passes), while reads through the
> parent compute the column with the parent's expression (f2*2 = 20). The two
> evaluations disagree, so the constraint the user declared is never applied
> to the value the user sees.
>
> Related, same parent/leaf evaluation mismatch: a row-level trigger declared
> on the parent with WHEN (OLD.f3 > 30) also fires based on the leaf
> expression, i.e. for rows whose parent-visible value is 20.
>
> Notes:
> - The WITH OPTIONS clause is the documented syntax for overriding a
> generated column's expression in a partition, so this combination is
> reachable by ordinary DDL.
Based on the documentation [1], the reported behavior is expected:
"For stored generated columns, the generation expression that is
actually applied during insert or update of a row is the one
associated with the table that the row is physically in. (This is
unlike the behavior for column defaults: for those, the default value
associated with the table named in the query applies.) For virtual
generated columns, the generation expression of the table named in the
query applies when a table is read."
During INSERT, the row is routed to the leaf partition gp2, and gp2
validates the CHECK constraint against its own generation expression
(10 * 22 = 220 > 30), so the INSERT succeeds. When reading from the
parent table gp, the parent's generation expression (10 * 2 = 20) is
used, whereas reading directly from gp2 uses gp2's generation
expression (10 * 22 = 220).
However, while testing this further, I noticed that setting
constraint_exclusion = on produces wrong results (0 rows) for the same
query:
postgres=# SET constraint_exclusion = partition;
SET
postgres=# SELECT f2, f3 FROM gp WHERE NOT (f3 > 30);
f2 | f3
----+----
10 | 20
(1 row)
postgres=# SET constraint_exclusion = on;
SET
postgres=# SELECT f2, f3 FROM gp WHERE NOT (f3 > 30);
f2 | f3
----+----
(0 rows)
The same problem also happens with a NOT NULL constraint on a virtual
generated column when constraint_exclusion = on (a query with "WHERE
f3 IS NULL" on the parent table gets falsely pruned to 0 rows even
when the parent's generation expression evaluates to NULL). This
happens because when constraint_exclusion = on,
relation_excluded_by_constraints() checks the parent relation's
constraints via get_relation_constraints() (with include_noinherit =
false, since rte->inh is true). Commit ce71993ae46 made
get_relation_constraints() to expand virtual generated columns in
constraint expressions using the relation's generation expression. For
the parent table gp, it expands CHECK (f3 > 30) using gp's generation
expression into (f2 * 2 > 30), which refutes the query qual NOT (f2 *
2 > 30) and prunes the entire query. However, when scanning a parent
table along with its children/partitions (!include_noinherit), we
cannot trust CHECK or NOT NULL constraints on virtual generated
columns of the parent relation, because each child table or partition
enforces those constraints against its own generation expression,
which may differ from the parent's expression.
A similar issue was recently fixed in commit d8444163d22 ("Don't trust
NOT NULL on virtual generated columns of partitioned tables"), which
taught var_is_nonnullable() to ignore catalog NOT NULL constraints on
virtual generated columns of partitioned tables.
To fix this in constraint exclusion, we can update
get_relation_constraints() so that when when scanning child tables
too(!include_noinherit), we ignore:
1. CHECK constraints that reference virtual generated columns (either
directly or via a whole-row Var), and
2. NOT NULL constraints on virtual generated columns.
When include_noinherit is true (scanning a plain table or an
individual leaf partition directly), those constraints can still be
safely expanded and used. Please find the attached patch implementing
this fix along with regression tests.
After the patch we can see consistent results with
constraint_exclusion=partition/on
postgres[4081660]=# set constraint_exclusion=partition;
SET
postgres[4081660]=# select f2,f3 from gp where not (f3 > 30);
f2 | f3
----+----
10 | 20
(1 row)
postgres[4081660]=# set constraint_exclusion=on;
SET
postgres[4081660]=# select f2,f3 from gp where not (f3 > 30);
f2 | f3
----+----
10 | 20
(1 row)
[1] https://www.postgresql.org/docs/current/ddl-generated-columns.html#:~:text=For%20stored%20generated%....
--
Regards,
Dilip Kumar
Google
Attachments:
v1-0001-Don-t-trust-constraints-on-virtual-generated-colu.patch (application/octet-stream, 7.4K) download
From 1c2e7c4f15367e4fe586d8e83686a4eab40c0eb8 Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumarb@google.com>
Date: Mon, 9 Nov 2026 10:44:56 +0000
Subject: [PATCH v1] Don't trust constraints on virtual generated columns of
parent tables
Commit ce71993ae46 taught get_relation_constraints() to expand virtual
generated columns in constraint expressions so that constraint exclusion
can match them against qual clauses.
However, that is not safe when scanning a parent table along with its
child tables or partitions (!include_noinherit). A child table or
partition can have a generation expression different from its parent's,
and it enforces CHECK and NOT NULL constraints against its own
expression, whereas a query on the parent computes the column using the
parent's expression. Consequently, rows read through the parent can
violate the parent's CHECK or NOT NULL constraints on a virtual
generated column. Expanding those constraints on the parent relation
using the parent's generation expression can therefore cause constraint
exclusion (when constraint_exclusion = on) to falsely prune the parent
relation and return wrong results.
To fix, make get_relation_constraints() ignore CHECK constraints that
reference virtual generated columns (directly or via a whole-row Var) as
well as NOT NULL constraints on virtual generated columns when
!include_noinherit. This is similar to the commit d8444163d22,
which taught var_is_nonnullable() not to trust NOT NULL constraints on
virtual generated columns of partitioned tables.
Authored-by: Dilip Kumar <dilipbalaut@gmail.com>
---
src/backend/optimizer/util/plancat.c | 48 ++++++++++++++++++-
.../regress/expected/generated_virtual.out | 45 +++++++++++++++++
src/test/regress/sql/generated_virtual.sql | 27 +++++++++++
3 files changed, 119 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 22e0e8b11f8..ccca4587a98 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1558,6 +1558,42 @@ get_relation_constraints(PlannerInfo *root,
cexpr = stringToNode(constr->check[i].ccbin);
+ /*
+ * If we are scanning child tables too, we cannot trust CHECK
+ * constraints that reference virtual generated columns. Each
+ * child table or partition enforces CHECK constraints against its
+ * own generation expression, which can differ from this
+ * relation's expression.
+ */
+ if (!include_noinherit && constr->has_generated_virtual)
+ {
+ Bitmapset *expr_attrs = NULL;
+ bool has_virtual = false;
+ int j = -1;
+
+ pull_varattnos(cexpr, 1, &expr_attrs);
+ while ((j = bms_next_member(expr_attrs, j)) >= 0)
+ {
+ AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber;
+
+ /*
+ * A whole-row Var also references any virtual generated
+ * columns of the relation.
+ */
+ if (attno == InvalidAttrNumber ||
+ (attno > 0 &&
+ TupleDescAttr(relation->rd_att, attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL))
+ {
+ has_virtual = true;
+ break;
+ }
+ }
+ bms_free(expr_attrs);
+
+ if (has_virtual)
+ continue;
+ }
+
/*
* Fix Vars to have the desired varno. This must be done before
* const-simplification because eval_const_expressions reduces
@@ -1600,8 +1636,18 @@ get_relation_constraints(PlannerInfo *root,
if (att->attnullability == ATTNULLABLE_VALID && !att->attisdropped)
{
Form_pg_attribute wholeatt = TupleDescAttr(relation->rd_att, i - 1);
- NullTest *ntest = makeNode(NullTest);
+ NullTest *ntest;
+
+ /*
+ * As above, we cannot trust a NOT NULL constraint on a
+ * virtual generated column when scanning child tables
+ * too.
+ */
+ if (!include_noinherit &&
+ wholeatt->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+ ntest = makeNode(NullTest);
ntest->arg = (Expr *) makeVar(varno,
i,
wholeatt->atttypid,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index b2f8e16e63b..99ea9b086ab 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1907,3 +1907,48 @@ SELECT a FROM gtestnn_parent2 WHERE a NOT IN (SELECT b FROM gtestnn_parent2);
(0 rows)
DROP TABLE gtestnn_parent2;
+-- Constraint exclusion must not trust CHECK or NOT NULL constraints on
+-- virtual generated columns of parent/partitioned tables, because a child
+-- partition can override the generation expression.
+CREATE TABLE gtest33p (
+ f2 int,
+ f3 int GENERATED ALWAYS AS (nullif(f2, 10) * 2) VIRTUAL NOT NULL,
+ CONSTRAINT gtest33p_ck CHECK (f3 > 30)
+) PARTITION BY RANGE (f2);
+CREATE TABLE gtest33p1 PARTITION OF gtest33p FOR VALUES FROM (0) TO (8);
+CREATE TABLE gtest33p2 PARTITION OF gtest33p
+ (f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL)
+ FOR VALUES FROM (8) TO (100);
+INSERT INTO gtest33p (f2) VALUES (10), (12);
+SET constraint_exclusion TO on;
+-- gtest33p1 is excluded by its own CHECK/NOT NULL constraints, while gtest33p2 is scanned
+EXPLAIN (COSTS OFF)
+SELECT f2, f3 FROM gtest33p WHERE NOT (f3 > 30);
+ QUERY PLAN
+----------------------------------------
+ Seq Scan on gtest33p2 gtest33p
+ Filter: ((NULLIF(f2, 10) * 2) <= 30)
+(2 rows)
+
+SELECT f2, f3 FROM gtest33p WHERE NOT (f3 > 30);
+ f2 | f3
+----+----
+ 12 | 24
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT f2, f3 FROM gtest33p WHERE f3 IS NULL;
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on gtest33p2 gtest33p
+ Filter: ((NULLIF(f2, 10) * 2) IS NULL)
+(2 rows)
+
+SELECT f2, f3 FROM gtest33p WHERE f3 IS NULL;
+ f2 | f3
+----+----
+ 10 |
+(1 row)
+
+RESET constraint_exclusion;
+DROP TABLE gtest33p;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index c4c4f3d5ef1..9579802cdb8 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -1014,3 +1014,30 @@ EXPLAIN (COSTS OFF)
SELECT a FROM gtestnn_parent2 WHERE a NOT IN (SELECT b FROM gtestnn_parent2);
SELECT a FROM gtestnn_parent2 WHERE a NOT IN (SELECT b FROM gtestnn_parent2);
DROP TABLE gtestnn_parent2;
+
+-- Constraint exclusion must not trust CHECK or NOT NULL constraints on
+-- virtual generated columns of parent/partitioned tables, because a child
+-- partition can override the generation expression.
+CREATE TABLE gtest33p (
+ f2 int,
+ f3 int GENERATED ALWAYS AS (nullif(f2, 10) * 2) VIRTUAL NOT NULL,
+ CONSTRAINT gtest33p_ck CHECK (f3 > 30)
+) PARTITION BY RANGE (f2);
+CREATE TABLE gtest33p1 PARTITION OF gtest33p FOR VALUES FROM (0) TO (8);
+CREATE TABLE gtest33p2 PARTITION OF gtest33p
+ (f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL)
+ FOR VALUES FROM (8) TO (100);
+INSERT INTO gtest33p (f2) VALUES (10), (12);
+
+SET constraint_exclusion TO on;
+-- gtest33p1 is excluded by its own CHECK/NOT NULL constraints, while gtest33p2 is scanned
+EXPLAIN (COSTS OFF)
+SELECT f2, f3 FROM gtest33p WHERE NOT (f3 > 30);
+SELECT f2, f3 FROM gtest33p WHERE NOT (f3 > 30);
+
+EXPLAIN (COSTS OFF)
+SELECT f2, f3 FROM gtest33p WHERE f3 IS NULL;
+SELECT f2, f3 FROM gtest33p WHERE f3 IS NULL;
+
+RESET constraint_exclusion;
+DROP TABLE gtest33p;
--
2.56.0.455.g6c5a503b38-goog
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19807: CHECK constraint not enforced on partitioned table
@ 2026-11-14 21:33 Joao Rito <joaovictor.rito@gmail.com>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joao Rito @ 2026-11-14 21:33 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Dilip,
I tested v1 on current master (206e719, cassert, tap tests) and it applies cleanly. With constraint_exclusion=on, "WHERE NOT (f3 > 30)" and "WHERE f3 IS NULL" on the parent returned 0 rows before the patch. After the patch they return (12, 24) and (10, NULL), and the plan scans gp2. The result also holds after a pg_dump and restore. The regress suite passes (243 subtests) and I saw no assertion failures in the server log.
What I did not test: the isolation and tap suites, pgindent, the docs, and a CHECK constraint with a whole-row reference (my attempt failed at DDL time, so I could not tell).
Small points: the line with TupleDescAttr(...) is longer than 80 columns, and the test could add a direct scan of the partition (include_noinherit true) to show that its own constraints are still used. Do you think the INSERT and trigger WHEN cases from the report need a separate follow-up?
Regards,
Joao Rito
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-11-14 21:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 06:17 BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu PG Bug reporting form <noreply@postgresql.org>
2026-11-09 10:49 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-14 21:33 ` Re: BUG #19807: CHECK constraint not enforced on partitioned table Joao Rito <joaovictor.rito@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox