Re: BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu

On Fri, Oct 30, 2026 at 12:42 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19807
> Logged by: chunling qin
> Email address: 303677365(at)qq(dot)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%20columns%2C%20the,when%20a%20table%20is%20read.

--
Regards,
Dilip Kumar
Google