Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned

Hi Shihao,

On Tue, Oct 13, 2026 at 6:00 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de>
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 <zhong950419(at)gmail(dot)com>
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