Hi Shihao,
On Tue, Oct 13, 2026 at 6:00 PM Álvaro Herrera <[email protected]>
wrote:
> I suspect we don't really know what other
> things this misfeature can break.
One more: with constraint_exclusion = on (not the default), a query
on the parent returns wrong results, on 18.6 too:
create table p (a int,
v int generated always as (nullif(a, 0)) virtual not null)
partition by list (a);
create table p1 partition of p
(v generated always as (a + 1) virtual) for values in (0);
insert into p values (0);
set constraint_exclusion = on;
select count(*) from p where v is null; -- 0, should be 1
The plan is "One-Time Filter: false": the parent's NOT NULL refutes
the qual, but the row read through p has v = NULL. A CHECK (v > 0)
on the parent does the same: count(*) where v < 0 gives 0, not 1.
Both on 18.6, 19beta4, 19rc1 and master (dfd7ede529f), so
d8444163d22 does not change it; with the default setting all give 1.
repro-ce-on.sql.txt is attached.
On Wed, Oct 14, 2026 at 12:39 AM shihao zhong <[email protected]>
wrote:
> With the patch, a virtual generated column of a partition must use the
> same expression as the parent. CREATE TABLE PARTITION OF and ATTACH
> PARTITION check this, and SET EXPRESSION can only change the whole
> partition tree. Stored columns and plain inheritance are unchanged.
I ran v1 on master and REL_19_STABLE, and the 18 version on
REL_18_STABLE, against 13 statements that create a divergent
expression: PARTITION OF (LIST, RANGE, HASH, DEFAULT), ATTACH, SET
EXPRESSION on a partition or on ONLY the parent, four multi-level
cases and two foreign-table partitions (paths.sql.txt). All 13 fail
on the three branches. The controls with the parent's expression,
STORED and INHERITS still work. Your query finds 7 mismatches
unpatched and 0 patched. make check passes on the three, and the
pg_upgrade TAP tests on master. Without the ATTACH check, 3 of the
13 get through, so the script catches that. On REL_19_STABLE v1
needs "git am -3" (check_for_pg_role_prefix is still in check.c).
> For existing databases, the attached query lists the partition columns
> that differ, and pg_upgrade on master and 19 runs the same check. Users
> can fix them with SET EXPRESSION on the parent, unless existing rows
> break NOT NULL under the parent's expression.
pg_upgrade --check from an 18.6 cluster to master and to 19 (both
patched) lists all four divergent columns I created. On patched 18
the existing ones keep the wrong result above until they are fixed.
One case where the fix fails on 18: SET EXPRESSION on a virtual
column errors out if the table has any CHECK constraint, even one
that does not involve the column, and also after the rows are fixed:
ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual
generated columns in tables with check constraints
f80bedd52b1 lifted this in 19; 18 still has it. So the pg_upgrade
hint fails for such tables in an 18 cluster. What worked on patched
18, in one transaction: drop the CHECK, SET EXPRESSION on the parent,
add the CHECK back. After that your query is empty and pg_upgrade
--check passes (legacy.sh.txt).
All of this is on one machine, cassert builds; 17 and older not run,
no check-world. Details are in measurements.txt.
Regards,
Manu
-- Wrong result with constraint_exclusion = on (not the default) when a
-- partition has its own VIRTUAL generation expression. The partition
-- enforces NOT NULL / CHECK with its own expression, a read through the
-- parent computes the parent's, and constraint exclusion trusts the
-- parent's constraints. The correct answer is 1 for each count.
-- Run with: psql -X -f repro-ce-on.sql.txt
-- With the v1 patch, the two CREATE TABLE ... PARTITION OF fail.
\pset null '(null)'
select current_setting('server_version') as server_version;
create table p (a int,
v int generated always as (nullif(a, 0)) virtual not null)
partition by list (a);
create table p1 partition of p
(v generated always as (a + 1) virtual) for values in (0);
insert into p values (0);
create table c (a int,
v int generated always as (-a) virtual check (v > 0))
partition by list (a);
create table c1 partition of c
(v generated always as (a) virtual) for values in (5);
insert into c values (5);
select a, v from p; -- v reads NULL through p
set constraint_exclusion = partition; -- the default
select count(*) as p_null from p where v is null; -- 1
select count(*) as c_neg from c where v < 0; -- 1
set constraint_exclusion = on;
select count(*) as p_null from p where v is null; -- 0, should be 1
select count(*) as c_neg from c where v < 0; -- 0, should be 1
explain (costs off) select count(*) from p where v is null;
reset constraint_exclusion;
-- Every DDL path that can give a partition a VIRTUAL generation expression
-- different from its parent's, each with a control that uses the parent's
-- expression and must keep working. Run with ON_ERROR_STOP off; the
-- expected outcome is in the comment of each statement:
-- [div] divergent: accepted on unpatched builds, should fail with the patch
-- [same] same expression: should succeed on every build
-- [keep] outside the patch's scope: should behave as before
-- At the end, the find-mismatches query lists what got through.
\pset null '(null)'
select current_setting('server_version') as server_version;
-- P1. CREATE TABLE ... PARTITION OF, LIST
create table l (a int, v int generated always as (a * 2) virtual)
partition by list (a);
create table l1 partition of l
(v generated always as (a * 3) virtual) for values in (1); -- [div]
create table l2 partition of l
(v generated always as (a * 2) virtual) for values in (2); -- [same]
create table l3 partition of l for values in (3); -- [same]
-- P2. RANGE and HASH
create table r (a int, v int generated always as (a * 2) virtual)
partition by range (a);
create table r1 partition of r
(v generated always as (a * 3) virtual) for values from (0) to (10); -- [div]
create table h (a int, v int generated always as (a * 2) virtual)
partition by hash (a);
create table h1 partition of h
(v generated always as (a * 3) virtual)
for values with (modulus 2, remainder 0); -- [div]
-- P3. ATTACH PARTITION, also with a different column order
create table l4 (a int, v int generated always as (a * 3) virtual);
alter table l attach partition l4 for values in (4); -- [div]
create table l5 (v int generated always as (a * 2) virtual, a int);
alter table l attach partition l5 for values in (5); -- [same]
-- P4. DEFAULT partition
create table l6 partition of l
(v generated always as (a * 3) virtual) default; -- [div]
-- P5. SET EXPRESSION
alter table l2 alter column v set expression as (a * 5); -- [div]
alter table only l alter column v set expression as (a * 5); -- [div]
alter table l alter column v set expression as (a * 7); -- [same]
select c.relname, pg_get_expr(d.adbin, d.adrelid) as expr
from pg_class c join pg_attrdef d on d.adrelid = c.oid
where c.oid = 'l'::regclass or c.oid in
(select inhrelid from pg_inherits where inhparent = 'l'::regclass)
order by 1;
-- P6. Multi-level: a partitioned partition
create table m (a int, b int, v int generated always as (a * 2) virtual)
partition by list (a);
create table m1 partition of m
(v generated always as (a * 3) virtual)
for values in (1) partition by list (b); -- [div]
create table m2 partition of m for values in (2)
partition by list (b); -- [same]
create table m2a partition of m2
(v generated always as (a * 3) virtual) for values in (1); -- [div]
create table m2b partition of m2 for values in (2); -- [same]
alter table m2 alter column v set expression as (a * 9); -- [div]
-- a standalone partitioned table whose own leaf matches it, attached
-- to a parent with a different expression
create table m3 (a int, b int, v int generated always as (a * 3) virtual)
partition by list (b);
create table m3a partition of m3 for values in (1); -- [same]
alter table m attach partition m3 for values in (3); -- [div]
-- P7. Foreign table as a partition (no handler needed for DDL)
create foreign data wrapper vgd_fdw;
create server vgd_srv foreign data wrapper vgd_fdw;
create foreign table f1 partition of l
(v generated always as (a * 3) virtual)
for values in (7) server vgd_srv; -- [div]
create foreign table f2 (a int, v int generated always as (a * 3) virtual)
server vgd_srv;
alter table l attach partition f2 for values in (8); -- [div]
-- P8. Unchanged by design: STORED and plain inheritance
create table s (a int, v int generated always as (a * 2) stored)
partition by list (a);
create table s1 partition of s
(v generated always as (a * 3) stored) for values in (1); -- [keep]
create table i (a int, v int generated always as (a * 2) virtual);
create table i1 (a int, v int generated always as (a * 3) virtual)
inherits (i); -- [keep]
alter table i1 alter column v set expression as (a * 4); -- [keep]
-- What got through: the patch's own find-mismatches query
select c.oid::pg_catalog.regclass as partition,
i.inhparent::pg_catalog.regclass as parent,
a.attname as column,
pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) as partition_expr,
pg_catalog.pg_get_expr(dp.adbin, dp.adrelid) as parent_expr
from pg_catalog.pg_class c
join pg_catalog.pg_inherits i on i.inhrelid = c.oid
join pg_catalog.pg_attribute a on a.attrelid = c.oid
join pg_catalog.pg_attribute pa on pa.attrelid = i.inhparent
and pa.attname = a.attname
join pg_catalog.pg_attrdef dc on dc.adrelid = a.attrelid
and dc.adnum = a.attnum
join pg_catalog.pg_attrdef dp on dp.adrelid = pa.attrelid
and dp.adnum = pa.attnum
where c.relispartition and a.attgenerated = 'v'
and pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) <>
pg_catalog.pg_get_expr(dp.adbin, dp.adrelid)
order by 1, 3;
#!/usr/bin/env bash
# Divergent partitions that already exist when the patch arrives.
# Usage: bash legacy.sh.txt OLD18=<bindir> NEW18=<bindir> [UP_LABEL=<bindir>
...]
# OLD18 unpatched 18.x: creates the divergent partitions
# NEW18 18.x with the REL_18 patch: same data directory, minor update
# UP_* newer major versions with the patch: pg_upgrade --check targets
# Output goes to stdout; every cluster lives in a temp dir removed at exit.
set -uo pipefail
old=${1#*=}; new18=${2#*=}; shift 2
tmp=$(mktemp -d "${TMPDIR:-/tmp}/vgdl.XXXXXX")
trap 'for d in "$tmp"/*/; do [ -f "$d/postmaster.pid" ] && \
"$new18/pg_ctl" -D "$d" -m immediate -w stop >/dev/null 2>&1; done; rm -rf
"$tmp"' EXIT
port=${PORT_BASE:-61600}
cat >"$tmp/legacy.sql" <<'EOF'
-- Divergent partitions created on an unpatched server (the state that
-- already exists in deployed databases).
create table p (a int,
v int generated always as (nullif(a, 0)) virtual not null)
partition by list (a);
create table p1 partition of p
(v generated always as (a + 1) virtual) for values in (0);
insert into p values (0);
create table c (a int,
v int generated always as (-a) virtual check (v > 0))
partition by list (a);
create table c1 partition of c
(v generated always as (a) virtual) for values in (5);
insert into c values (5);
-- divergent, but no constraint is violated through the parent
create table b (a int, v int generated always as (a * 2) virtual)
partition by list (a);
create table b1 partition of b
(v generated always as (a * 3) virtual) for values in (1);
insert into b values (1);
-- divergent, with a CHECK constraint that does not involve v
create table k (a int check (a > 0), v int generated always as (a * 2) virtual)
partition by list (a);
create table k1 partition of k
(v generated always as (a * 3) virtual) for values in (1);
insert into k values (1);
EOF
cat >"$tmp/legacy-check.sql" <<'EOF'
\pset null '(null)'
select current_setting('server_version') as server_version;
-- mismatches as the patch's query reports them
select c.oid::pg_catalog.regclass as partition,
a.attname as column,
pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) as partition_expr,
pg_catalog.pg_get_expr(dp.adbin, dp.adrelid) as parent_expr
from pg_catalog.pg_class c
join pg_catalog.pg_inherits i on i.inhrelid = c.oid
join pg_catalog.pg_attribute a on a.attrelid = c.oid
join pg_catalog.pg_attribute pa on pa.attrelid = i.inhparent
and pa.attname = a.attname
join pg_catalog.pg_attrdef dc on dc.adrelid = a.attrelid
and dc.adnum = a.attnum
join pg_catalog.pg_attrdef dp on dp.adrelid = pa.attrelid
and dp.adnum = pa.attnum
where c.relispartition and a.attgenerated = 'v'
and pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) <>
pg_catalog.pg_get_expr(dp.adbin, dp.adrelid)
order by 1, 2;
-- the wrong result: correct answer is 1 for both
set constraint_exclusion = partition;
select 'ce=partition' as setting, (select count(*) from p where v is null) as
p_null,
(select count(*) from c where v < 0) as c_neg;
set constraint_exclusion = on;
select 'ce=on' as setting, (select count(*) from p where v is null) as p_null,
(select count(*) from c where v < 0) as c_neg;
reset constraint_exclusion;
EOF
start() { "$1/pg_ctl" -D "$2" -l "$2.log" -w \
-o "-p $port -k $tmp -c listen_addresses=''" start >/dev/null; }
stop() { "$1/pg_ctl" -D "$2" -m fast -w stop >/dev/null; }
sql() { local bin=$1 db=$2; shift 2
"$bin/psql" -X -h "$tmp" -p "$port" -U postgres -d "$db" "$@" 2>&1; }
say() { printf '\n### %s\n' "$*"; }
say "1. unpatched $("$old/postgres" --version): create divergent partitions"
"$old/initdb" -D "$tmp/old" -U postgres -A trust >/dev/null
start "$old" "$tmp/old"
"$old/createdb" -h "$tmp" -p "$port" -U postgres legacy
sql "$old" legacy -q -f "$tmp/legacy.sql"
sql "$old" legacy -f "$tmp/legacy-check.sql"
say "2. pg_dump of the unpatched server: how the partitions are emitted"
"$old/pg_dump" -h "$tmp" -p "$port" -U postgres legacy >"$tmp/dump-old.sql"
grep -nE 'CREATE TABLE|ATTACH PARTITION|GENERATED ALWAYS' "$tmp/dump-old.sql" \
| sed 's/^/ /'
stop "$old" "$tmp/old"
for spec in "$@"; do
label=${spec%%=*}; bin=${spec#*=}
say "3. pg_upgrade --check, old cluster as created in 1 -> $label"
"$bin/initdb" -D "$tmp/new-$label" -U postgres -A trust >/dev/null
( cd "$tmp" && "$bin/pg_upgrade" --check -U postgres -b "$old" -B "$bin" \
-d "$tmp/old" -D "$tmp/new-$label" -p $((port + 1)) -P $((port + 2)) \
-s "$tmp" ) 2>&1 | grep -vE '^\s*$' | sed 's/^/ /'
f=$(find "$tmp/new-$label" -name partition_virtual_generated_columns.txt |
head -1)
[ -n "$f" ] && { echo " report file:"; sed 's/^/ /' "$f"; }
done
say "4. same data directory started with $("$new18/postgres" --version) + patch"
start "$new18" "$tmp/old"
sql "$new18" legacy -f "$tmp/legacy-check.sql"
say "5. restore the unpatched dump (2) into the patched server"
"$new18/createdb" -h "$tmp" -p "$port" -U postgres restored_old
sql "$new18" restored_old -q -f "$tmp/dump-old.sql" | grep -E 'ERROR|DETAIL' \
| sed 's/^/ /'
sql "$new18" restored_old -f "$tmp/legacy-check.sql"
say "6. dump with the patched pg_dump, restore into the patched server"
"$new18/pg_dump" -h "$tmp" -p "$port" -U postgres legacy >"$tmp/dump-new.sql"
"$new18/createdb" -h "$tmp" -p "$port" -U postgres restored_new
sql "$new18" restored_new -q -f "$tmp/dump-new.sql" | grep -E 'ERROR|DETAIL' \
| sed 's/^/ /'
say "7. remedy suggested by the patch: SET EXPRESSION on the parent"
for t in "b:(a * 2)" "p:(nullif(a, 0))" "c:(-a)" "k:(a * 2)"; do
echo " $t"
sql "$new18" legacy -c "alter table ${t%%:*} alter column v set expression as
${t#*:}" \
| sed 's/^/ /'
done
say "7b. after deleting the rows that violate the parent's constraints"
sql "$new18" legacy -c 'delete from p1' -c 'delete from c1' | sed 's/^/ /'
for t in "p:(nullif(a, 0))" "c:(-a)"; do
echo " $t"
sql "$new18" legacy -c "alter table ${t%%:*} alter column v set expression as
${t#*:}" \
| sed 's/^/ /'
done
say "7c. drop the CHECK, SET EXPRESSION, add the CHECK back"
sql "$new18" legacy -c 'begin' \
-c 'alter table c drop constraint c_v_check' \
-c 'alter table c alter column v set expression as (-a)' \
-c 'alter table c add constraint c_v_check check (v > 0)' \
-c 'alter table k drop constraint k_a_check' \
-c 'alter table k alter column v set expression as (a * 2)' \
-c 'alter table k add constraint k_a_check check (a > 0)' \
-c 'commit' | sed 's/^/ /'
sql "$new18" legacy -f "$tmp/legacy-check.sql"
stop "$new18" "$tmp/old"
for spec in "$@"; do
label=${spec%%=*}; bin=${spec#*=}
say "8. pg_upgrade --check after 7c -> $label"
rm -rf "$tmp/new-$label"
"$bin/initdb" -D "$tmp/new-$label" -U postgres -A trust >/dev/null
( cd "$tmp" && "$bin/pg_upgrade" --check -U postgres -b "$new18" -B "$bin" \
-d "$tmp/old" -D "$tmp/new-$label" -p $((port + 1)) -P $((port + 2)) \
-s "$tmp" ) 2>&1 | grep -iE 'virtual|clusters are compatible|fatal' \
| sed 's/^/ /'
done
Partition with its own VIRTUAL generation expression: wrong result with
constraint_exclusion = on, and shihao's v1 against every DDL path
=========================================================================
Correctness only, no timings. One machine (x86_64, Linux), gcc,
--enable-cassert --enable-debug, CFLAGS=-O2. 17 and older not run
(no virtual generated columns before 18).
Builds
18.6 REL_18_6 release
19beta4 REL_19_BETA4 (before d8444163d22)
19rc1 REL_19_RC1 (contains d8444163d22)
master dfd7ede529f (2026-10-25)
m-v1 master + v1-0001 (applies cleanly)
19-v1 REL_19_STABLE 2b491ff1cd2 + v1-0001; plain "git am" fails on
the src/bin/pg_upgrade/check.c prototype context
(check_for_pg_role_prefix is still there in 19);
"git am -3" applies it without conflicts
18-v1 REL_18_STABLE 1735ad557d3 + nocfbot-v1-REL_18-0001
mutant m-v1 with the CheckPartitionVirtualGeneratedExprs() call in
MergeAttributesIntoExisting() removed (ATTACH unchecked)
1. repro-ce-on.sql.txt (correct answer: 1 in every cell)
-------------------------------------------------------
18.6 19beta4 19rc1 master
NOT NULL: count(*) WHERE v IS NULL
constraint_exclusion=partition 1 1 1 1
constraint_exclusion=on 0 0 0 0
CHECK (v > 0): count(*) WHERE v < 0
constraint_exclusion=partition 1 1 1 1
constraint_exclusion=on 0 0 0 0
Plan with "on": Result, One-Time Filter: false, on all four.
m-v1, 19-v1, 18-v1: both CREATE TABLE ... PARTITION OF are rejected,
so the case cannot be built.
2. paths.sql.txt: DDL paths that create a divergent expression
--------------------------------------------------------------
A = accepted, R = rejected 18.6/master m-v1/19-v1/18-v1
P1 CREATE TABLE ... PARTITION OF, LIST A R
P2 same, RANGE and HASH A R
P3 ATTACH PARTITION A R
P4 DEFAULT partition A R
P5 SET EXPRESSION on a partition A R
SET EXPRESSION on ONLY parent A R
P6 partitioned partition, own expression A R
leaf under it, own expression A R
SET EXPRESSION on the middle level A R
ATTACH of a partitioned table (its leaf
matches it, it differs from the parent) A R
P7 CREATE FOREIGN TABLE ... PARTITION OF A R
ATTACH of a foreign table A R
13 divergent statements: 13 errors on each patched build, the same
13 statements, and 0 on 18.6/master. Every control with the
parent's expression is accepted on all builds: CREATE ... PARTITION
OF with the same or no expression, ATTACH with a different column
order, recursive SET EXPRESSION on the parent, a leaf under a
partitioned partition. P8 (STORED partitions, INHERITS children,
SET EXPRESSION on an INHERITS child) is accepted on all builds.
Final query (the one from nocfbot-find-mismatched-virtual-exprs.sql):
7 rows on 18.6/master, 0 rows on each patched build.
mutant: the 3 ATTACH statements are accepted again (10 errors), and
the final query lists the 2 that survive to the end (m3, f2).
3. legacy.sh.txt: divergent partitions that already exist
---------------------------------------------------------
Created on 18.6: p1 (NOT NULL broken through the parent), c1 (CHECK
on v broken), b1 (no constraint), k1 (CHECK (a > 0), not on v).
1 18.6: the query lists 4; constraint_exclusion=on gives 0 and 0.
3 pg_upgrade --check 18.6 -> m-v1 and -> 19-v1: fatal, the report
file lists the 4 columns, on both.
4 same data directory started with 18-v1: the query lists 4;
constraint_exclusion=on still gives 0 and 0 (the patch prevents
new ones; existing ones stay until fixed).
5 18.6 pg_dump restored into 18-v1: each of the 4 ATTACH PARTITION
fails, as the v1 mail says.
7 ALTER TABLE <parent> ALTER COLUMN v SET EXPRESSION (on 18-v1):
b ok
p ERROR: column "v" of relation "p1" contains null values
c ERROR: ALTER TABLE / SET EXPRESSION is not supported for
virtual generated columns in tables with check constraints
k same ERROR as c, although its CHECK does not involve v
7b after deleting the violating rows: p ok, c same ERROR.
The restriction is in REL_18_STABLE ATExecSetExpression()
(num_check > 0 on the relation, any CHECK); f80bedd52b1 lifted
it in 19 and it was not back-patched.
7c in one transaction: DROP CONSTRAINT, SET EXPRESSION on the
parent, ADD CONSTRAINT again, for c and k: ok; the query
lists 0.
8 pg_upgrade --check after 7c -> m-v1 and -> 19-v1: compatible.
4. Tests on the patched builds
------------------------------
make check: m-v1 239/239, 19-v1 239/239, 18-v1 231/231.
src/bin/pg_upgrade TAP on m-v1 (--enable-tap-tests, PROVE_FLAGS=-j1):
8 files, 160 tests, PASS.
Not run: check-world, meson/clang, cross-version pg_upgrade TAP.
How to repeat
psql -X -f repro-ce-on.sql.txt (any database)
psql -X -f paths.sql.txt (fresh database)
bash legacy.sh.txt OLD18=<18.x bindir> NEW18=<patched 18 bindir> \
<label>=<patched 19 or master bindir> ...
