pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Don't trust NOT NULL on virtual generated columns of partitioned tables
Commit 383eb21eb taught the planner to convert a NOT IN sublink to an
anti-join when the sub-select's outputs are provably non-nullable.
Since the sub-select has not been through any planner processing at
that point, a reference to a virtual generated column is still a plain
Var, and query_outputs_are_not_nullable() relied on the column's NOT
NULL constraint to prove it non-nullable.
That is not safe for a partitioned table. A partition can have a
generation expression different from its parent's, and it enforces the
NOT NULL constraint against its own expression, whereas a query on the
parent computes the column using the parent's expression. So the
column can be NULL when read through the parent despite the
constraint, and the anti-join would then produce wrong results, as it
returns rows that NOT IN should have rejected.
To fix, make var_is_nonnullable() ignore the NOT NULL constraint on a
virtual generated column of a partitioned table when consulting the
catalogs. The other sources of NOT NULL information are only used
after virtual generated columns have been expanded, so they are not
affected.
Author: Shihao Zhong <zhong950419@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Discussion: /messages/by-id/CAGRkXqR7VXacmRP41UBaM5u4aseeCH5ECVmQgn_YS=L7=xdhTw@mail.gmail.com
Backpatch-through: 19
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/d8444163d22e576c9f284a55eab7999ba67c4ceb
Modified Files
--------------
src/backend/optimizer/util/clauses.c | 12 ++++++++++++
src/test/regress/expected/generated_virtual.out | 21 +++++++++++++++++++++
src/test/regress/sql/generated_virtual.sql | 10 ++++++++++
3 files changed, 43 insertions(+)
On 2026-Oct-10, Richard Guo wrote:
Don't trust NOT NULL on virtual generated columns of partitioned tables
[...]
That is not safe for a partitioned table. A partition can have a
generation expression different from its parent's, and it enforces the
NOT NULL constraint against its own expression, whereas a query on the
parent computes the column using the parent's expression. So the
column can be NULL when read through the parent despite the
constraint, and the anti-join would then produce wrong results, as it
returns rows that NOT IN should have rejected.
I find this very surprising. Are we sure that it makes sense for a
partition to have a generating expression that differs from the one in
the parent table?
Copying from the test case you added,
CREATE TABLE gtestnn_parent2 (a int NOT NULL, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL) PARTITION BY LIST (a);
CREATE TABLE gtestnn_child2 PARTITION OF gtestnn_parent2 (b GENERATED ALWAYS AS (a + 1) VIRTUAL) FOR VALUES IN (0);
INSERT INTO gtestnn_parent2 VALUES (0);
we read a NULL value from a column that's marked as not-null, which
shouldn't happen.
alvherre=# select * from gtestnn_parent2 ;
a | b
---+--------
0 | (null)
(1 row)
alvherre=# \d gtestnn_parent2
Partitioned table "public.gtestnn_parent2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
b | integer | | not null | generated always as (NULLIF(a, 0))
Partition key: LIST (a)
Number of partitions: 1 (Use \d+ to list them.)
The row is of course different if I read it directly from the partition.
alvherre=# select * from gtestnn_child2 ;
a | b
---+---
0 | 1
(1 row)
I didn't try but I imagine that allowing partitions to have a different
generating expression than the parent can break all kinds of constraint,
not just not-null ones, if generating expressions are "broken" enough.
Maybe we should disallow expressions to be different, and force users to
use STORED virtual columns if they want to do that. This seems
dangerous.
Alternatively, you could argue that at read time the row should get its
virtual column values using the expressions from the children rather
than for the parent.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Las navajas y los monos deben estar siempre distantes" (Germán Poo)
Import Notes
Reply to msg id not found: E1xFKs5-0000000116b-2Cge@gemulon.postgresql.org
On Tue, Oct 13, 2026 at 9:43 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
I find this very surprising. Are we sure that it makes sense for a
partition to have a generating expression that differs from the one in
the parent table?
No, I don't think it makes sense for a partition to have a different
virtual generation expression than its parent. I raised this exact
same complaint in [1]/messages/by-id/CAMbWs4-dfVUXSu1fwfTd8HZypKsSN1uYjEPfMDb6bqB58Pas4g@mail.gmail.com, the paragraph starting with "Regarding #4",
quoted below:
Regarding #4, the fix is to not trust NOT NULL on a virtual generated
column of a partitioned table. But I don't like this fix at all. I
think the real problem is that a partition is allowed to have a
different virtual generation expression than its parent. The
constraint is enforced with the partition's expression, while a query
on the parent computes the column with the parent's expression. So
even on v18, a NOT NULL column can read as NULL through the parent,
and the same goes for CHECK constraints. That seems fragile to me,
and I suspect it will bite us again. CC'ing Peter here.
I'm not sure why virtual generated columns are designed this way, or
if this is required by the SQL standard. But I can see it breaks a
lot of things, and what this commit does is just to work-around one of
the things that design breaks.
[1]: /messages/by-id/CAMbWs4-dfVUXSu1fwfTd8HZypKsSN1uYjEPfMDb6bqB58Pas4g@mail.gmail.com
- Richard
Hi Alvaro and Richard,
Álvaro Herrera <alvherre@kurilemu.de> writes:
we read a NULL value from a column that's marked as not-null, which
shouldn't happen.
One data point, this part is not new in 19. Your example reads the
same NULL through the parent on 18.6. My initial idea is to just fix
the PG19 regression instead of addressing the entire problem.
But I can see it breaks a
lot of things, and what this commit does is just to work-around one of
the things that design breaks.
Agree, I think there is a design question here and a more completed
fix should target 20.
Thanks,
Shihao
On 2026-Oct-13, Richard Guo wrote:
On Tue, Oct 13, 2026 at 9:43 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
I find this very surprising. Are we sure that it makes sense for a
partition to have a generating expression that differs from the one in
the parent table?No, I don't think it makes sense for a partition to have a different
virtual generation expression than its parent. I raised this exact
same complaint in [1], the paragraph starting with "Regarding #4",
quoted below:Regarding #4, the fix is to not trust NOT NULL on a virtual generated
column of a partitioned table. But I don't like this fix at all. I
think the real problem is that a partition is allowed to have a
different virtual generation expression than its parent.
It looks like we reached the same conclusion. I propose we restrict
this. Ideally we'd do that at least starting with 19. (I would in fact
go even further and say that we should add restrictions to all affected
stable branches, and provide SQL scripts that users can run to find and
remove those that already exist there.) I don't have a patch though,
but I imagine it should be easy.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)
Hi,
On 2026-Oct-13, shihao zhong wrote:
Álvaro Herrera <alvherre@kurilemu.de> writes:
we read a NULL value from a column that's marked as not-null, which
shouldn't happen.One data point, this part is not new in 19. Your example reads the
same NULL through the parent on 18.6.
Right -- I was running the reproducer in 18 as well.
My initial idea is to just fix the PG19 regression instead of
addressing the entire problem.
Yes, Richard committed this fix already -- I was replying to his commit
message of your fix.
/messages/by-id/flat/E1xFKs5-0000000116b-2Cge@gemulon.postgresql.org
But I can see it breaks a lot of things, and what this commit does
is just to work-around one of the things that design breaks.Agree, I think there is a design question here and a more completed
fix should target 20.
This is where I disagree ... IMO this is serious problem whose fix
should be back-patched. I suspect we don't really know what other
things this misfeature can break.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)
Hi Álvaro,
This is where I disagree ... IMO this is serious problem whose fix
should be back-patched.
Fair enough, I agree. Here is a patch for master and 19, and a version
for 18.
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.
On why it works this way today. Before 8bf6ec3ba3a, a child had to use
the parent's expression. That commit relaxed the rule when stored
columns were the only kind, and for them it is fine. Virtual columns
kept the relaxed rule, but they are computed with the expression of the
table named in the query, so the old rule fits them better.
provide SQL scripts that users can run to find and
remove those that already exist there.
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.
For 18 there is one cost. A dump of a database that already has such
partitions will fail to attach them when restored into a patched 18.
So the release notes would need to ask users to run the query first.
Thanks,
Shihao
Attachments:
t254178_6v1-0001-Require-partitions-to-use-the-parent-s-virtual-ge.patchapplication/octet-stream; name=v1-0001-Require-partitions-to-use-the-parent-s-virtual-ge.patchDownload+253-77
nocfbot-v1-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v1-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchDownload+197-61
Hi Shihao,
On Tue, Oct 13, 2026 at 6:00 PM Álvaro Herrera <alvherre@kurilemu.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@gmail.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
Hi Manu,
Thanks for testing the pg_upgrade, I forgot about that.
I don't think the logic is wrong here, but maybe we can make some
improvement to the error message. v2 changes
on the partitioned table of each column listed in the file
=>
on the partitioned table of each column. In version 18 this is not
allowed on a table with CHECK constraints, so drop them first and
add them back afterwards. A list of the problem columns is in the
file
Thanks,
Shihao
Attachments:
t254178_8nocfbot-v2-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v2-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchDownload+197-61
v2-0001-Require-partitions-to-use-the-parent-s-virtual-ge.patchapplication/octet-stream; name=v2-0001-Require-partitions-to-use-the-parent-s-virtual-ge.patchDownload+256-77
On 2026-Oct-26, shihao zhong wrote:
Hi Manu,
Thanks for testing the pg_upgrade, I forgot about that.
I don't think the logic is wrong here, but maybe we can make some
improvement to the error message. v2 changeson the partitioned table of each column listed in the file
=>
on the partitioned table of each column. In version 18 this is not
allowed on a table with CHECK constraints, so drop them first and
add them back afterwards. A list of the problem columns is in the
file
I think the doc changes here are not ideal; they kinda suggest that there are
numerous different generated column types for which you can have different
generation expressions except for one special case -- rather than one case yes,
one case no. How about something like this?
If a parent column is a generated column, its child column must also
- be a generated column of the same kind (stored or virtual); however,
- the child column can have a different generation expression, except
- that a virtual generated column of a partition must have the same
- generation expression as the partitioned table.
+ be a generated column of the same kind (stored or virtual).
+ In addition, for virtual generated columns, the child column's
+ generation expression must be identical to that of the parent.
+ For stored generated columns, they are allowed to differ.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"I'm impressed how quickly you are fixing this obscure issue. I came from
MS SQL and it would be hard for me to put into words how much of a better job
you all are doing on [PostgreSQL]."
Steve Midgley, http://archives.postgresql.org/pgsql-sql/2008-08/msg00000.php
Also, the comment in MergeAttributes is ... not wrong, but doesn't
really flow with the code, I think. How about a change like this?
* Same rules as above: generated-ness has to match the
- * parent, but the contents of the generation expression
- * can be different, except for virtual generated columns
- * (see CheckPartitionVirtualGeneratedExprs).
+ * parent. We don't check the expression itself here;
+ * CheckPartitionVirtualGeneratedExprs will do so.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Hi Álvaro,
Thanks for both rewrites. v3 follows your suggestion for doc and comment.
"I'm impressed how quickly you are fixing this obscure issue. I came from
MS SQL and it would be hard for me to put into words how much of a better
job
you all are doing on [PostgreSQL]."
Also, just curious. Your emails often end with a nice quote. Does a tool
pick those for you?
Attachments:
t254562_12v3-0001-Require-partitions-to-use-the-parent-s-virtual-gener.patchapplication/octet-stream; name=v3-0001-Require-partitions-to-use-the-parent-s-virtual-gener.patchDownload+258-79
nocfbot-v3-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v3-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchDownload+199-63
Hi Shihao,
On Wed, Nov 11, 2026 at 11:36 PM shihao zhong <zhong950419@gmail.com>
wrote:
Thanks for both rewrites. v3 follows your suggestion for doc and comment.
v3 works on master (77bba5454f9), REL_19_STABLE (dfffe8da630) and, in
its 18 version, REL_18_STABLE (2ea5222db7c): the 13 divergent
statements of my Oct 26 paths.sql all fail, the controls pass, and so
do make check and, on master and 19, the pg_upgrade TAP tests. The
new pg_upgrade text falls short in two cases on 18.
On Mon, Oct 26, 2026 at 11:10 PM shihao zhong <zhong950419@gmail.com>
wrote:
I don't think the logic is wrong here, but maybe we can make some
improvement to the error message. v2 changeson the partitioned table of each column listed in the file
=>
on the partitioned table of each column. In version 18 this is not
allowed on a table with CHECK constraints, so drop them first and
add them back afterwards. A list of the problem columns is in the
file
Sorry for not answering this sooner. On REL_18_STABLE, with and
without the patch, I did only what the text says, and with the CHECK
on the partitioned table pg_upgrade --check to master and 19 then
passes. It does not when:
- the CHECK is only on the partition: SET EXPRESSION on the parent
fails ("not supported for virtual generated columns in tables with
check constraints"), and the parent has no CHECK to drop;
- the divergent partition is a leaf under a partitioned partition:
with the 18 patch, SET EXPRESSION on its parent (the middle table)
fails ("cannot alter ... of partition"); its hint, also on master,
says to alter the partitioned table, which it is; the root works.
Rows that break NOT NULL or a CHECK under the parent's expression
stop it too, with clear errors. Maybe:
on the top-level partitioned table of each column. In version 18
this is not allowed if any table in that partition tree has CHECK
constraints, so drop them first and add them back afterwards. Rows
that violate NOT NULL or CHECK with the new expression must be fixed
first. A list of the problem columns is in the file:
From unpatched REL_19_STABLE the check fires too, and SET EXPRESSION
on the parent is enough. The script and its output are attached.
Regards,
Manu
Hi Manu,
Thanks for trying both cases on 18 and for the new text. v4 uses your
pg_upgrade text as is.
Thanks,
Shihao
Attachments:
t254562_14v4-0001-Require-partitions-to-use-the-parent-s-virtual-gener.patchapplication/octet-stream; name=v4-0001-Require-partitions-to-use-the-parent-s-virtual-gener.patchDownload+259-79
nocfbot-v4-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v4-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patchDownload+199-63
Hello
With this restriction master can't create divergent partitions anymore, and pg_upgrade refuses them, so is d8444163d still needed on master after this patch?
There are still at least two ways to still end up with a problematic partition. In both cases the new check then rejects a pg_dump of the cluster at restore:
1. Explicit vs implicit cast
CREATE TABLE q (k int, x numeric, b numeric(10,0) GENERATED ALWAYS AS (x) VIRTUAL) PARTITION BY RANGE (k);
CREATE TABLE q1 (k int, x numeric, b numeric(10,0) GENERATED ALWAYS AS (x::numeric(10,0)) VIRTUAL);
ALTER TABLE q ATTACH PARTITION q1 FOR VALUES FROM (0) TO (10); -- accepted
INSERT INTO q VALUES (1, 1.25);
ALTER TABLE q ALTER COLUMN b TYPE numeric(10,2);
SELECT (SELECT b FROM q) AS via_parent, (SELECT b FROM q1) AS direct;
via_parent | direct
------------+--------
1.25 | 1.00
After this q has "x" and q1 has "(x)::numeric(10,0)", and restoring pg_dump -t q -t q1 fails:
ERROR: virtual generated column "b" of partition "q1" must have the same generation expression as the partitioned table
equal() ignores CoercionForm, so the check accepts the pair, but ALTER TYPE re-coerces each table after strip_implicit_coercions(), which only removes the parent's cast.
2. ONLY with a detach-pending partition
+ if (!recurse &&
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+ find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
find_inheritance_children() skips detach-pending partitions.
After a DETACH PARTITION f1 CONCURRENTLY that got cancelled, ALTER TABLE ONLY f ALTER COLUMN b SET EXPRESSION AS (a3) succeeds while f1 keeps a2.
Also, the documentation has one more leftover:
ddl.sgml, a few lines below the paragraph v4 changes, still says:
from the parent. <command>ALTER TABLE ... ATTACH PARTITION</command>
will insist that parent and child columns already match as to
generation status, but it will not require their generation
expressions to match.
And there's a similar issue which I think also needs a fix together with this:
Plain inheritance has the same wrong results, both on master and with the patch:
CREATE TABLE cp (a int, b int GENERATED ALWAYS AS (a - 10) VIRTUAL CHECK (b > 0));
CREATE TABLE cc (b int GENERATED ALWAYS AS (a + 100) VIRTUAL) INHERITS (cp);
INSERT INTO cc VALUES (1);
SELECT a, b FROM cp; -- 1 | -9
SET constraint_exclusion = on;
SELECT a, b FROM cp WHERE b < 0; -- 0 rows
Hi Zsolt,
Thanks for your review, I appreciate it.
With this restriction master can't create divergent partitions
anymore, and pg_upgrade refuses them, so is d8444163d still needed on
master after this patch?
19 still needs it. A 19 cluster may already have such partitions,
and a minor update does not run pg_upgrade.
1. Explicit vs implicit cast
v5 compares the deparsed text, which is what pg_dump writes.
2. ONLY with a detach-pending partition
The recursive SET EXPRESSION skipped those partitions too.
ONLY now counts them as children. Without ONLY, the command asks to
finish the detach first.
ddl.sgml, a few lines below the paragraph
fixed.
Plain inheritance has the same wrong results, both on master and with
the patch:
v5 applies the same rule to plain inheritance, so your cc example now
fails at CREATE TABLE. pg_upgrade refuses such children too.
I also found CREATE TABLE ... (LIKE ...) INHERITS skips these checks.
I think it needs to have its own thread.
Thanks,
Shihao
Attachments:
t254562_16nocfbot-v5-REL_18-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v5-REL_18-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchDownload+376-94
v5-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=v5-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchDownload+447-111
Thanks for the update!
+-- a child with two parents cannot have its expression changed through one
+CREATE TABLE gtesty (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestz (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestyz () INHERITS (gtesty, gtestz);
+NOTICE: merging multiple inherited definitions of column "a"
+NOTICE: merging multiple inherited definitions of column "b"
+ALTER TABLE gtesty ALTER COLUMN b SET EXPRESSION AS (a * 3); -- error
But it can with a combined alter:
CREATE TABLE q (a int, b int GENERATED ALWAYS AS (nullif(a, 1)) VIRTUAL NOT NULL);
CREATE TABLE p (a int);
CREATE TABLE c () INHERITS (p, q);
ALTER TABLE p ADD COLUMN b int GENERATED ALWAYS AS (nullif(a, 1)) VIRTUAL,
ALTER COLUMN b SET EXPRESSION AS (a); -- accepted
Also, in the above example if we try to alter the child instead we get:
ALTER TABLE gtestyz ALTER COLUMN b SET EXPRESSION AS (a * 3);
-- ERROR: cannot alter inherited column "b"
-- HINT: Alter the generation expression of the top-level parent table instead.
But that hint won't work, as the testcase shows.
Hi Zsolt,
Thanks for catching both, v6 attached.
But it can with a combined alter:
The check ran before the ADD COLUMN, so the column did not exist yet.
v6 runs it when SET EXPRESSION executes, and your example now fails
like the two statement form.
But that hint won't work, as the testcase shows.
v6 gives the hint only for partitions, since a partition always has a
single top-level parent.
A simpler option is to reject the combined command, as ALTER COLUMN
TYPE does for a column added by the same command. I'm happy to switch
if that is preferred.
Thanks,
Shihao
Attachments:
t254562_18v6-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=v6-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchDownload+496-114
nocfbot-v6-REL_18-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchapplication/octet-stream; name=nocfbot-v6-REL_18-0001-Require-child-tables-to-use-the-parent-s-virtual-gen.patchDownload+425-97
Hi Shihao,
On Thu, Nov 19, 2026 at 12:01 AM shihao zhong <zhong950419@gmail.com>
wrote:
Thanks for catching both, v6 attached.
v6 passes everything we had, on master (b443bc7406f), REL_19_STABLE
(b1bc8f8ea5d) and, in its 18 version, REL_18_STABLE (ecfe3a44dab).
Two new cases, both on trees that an unpatched 18 or 19 left
divergent, after the minor update.
1. A child with two parents. The pg_upgrade text says to run SET
EXPRESSION on the top-level parent, which works on unpatched 18 but
not with the patch:
-- unpatched 18
CREATE TABLE ip (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL);
CREATE TABLE iq (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL);
CREATE TABLE ic (v int GENERATED ALWAYS AS (x * 3) VIRTUAL)
INHERITS (ip, iq);
-- 18 with the patch; the same on 19 with the master patch
ALTER TABLE ip ALTER COLUMN v SET EXPRESSION AS (x * 2);
ERROR: cannot alter inherited column "v" of relation "ic"
The same for iq. If the parents' expressions differ (x * 2 and
x * 3, the child giving its own, as 18's ddl.sgml asks), the text
does not work on any branch, unpatched included. What works on all
of them is NO INHERIT from all parents but one, SET EXPRESSION, and
INHERIT again. Maybe, after "add them back afterwards.":
A child table with more than one parent must first be removed from
all but one with NO INHERIT, and added back once all its parents
have the same expression.
2. With the patch, adding a virtual column to the parent of such a
tree fails, naming the old column:
-- unpatched 18
CREATE TABLE ta (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL)
PARTITION BY LIST (x);
CREATE TABLE ta1 PARTITION OF ta
(v GENERATED ALWAYS AS (x * 3) VIRTUAL) FOR VALUES IN (1);
-- 18 with the patch
ALTER TABLE ta ADD COLUMN w int GENERATED ALWAYS AS (x + 1) VIRTUAL;
ERROR: column "v" of relation "ta1" must have the same generation
expression as in parent relation "ta"
The check after the recursion in ATExecAddColumn (tablecmds.c:8347
on master, 8285 on 18) compares every virtual column of the parent,
but only the one being added is new to the child; ATTACH and INHERIT
are the ones that bring a whole new parent. Maybe compare only that
column there?
Regards,
Manu
On 2026-Nov-11, shihao zhong wrote:
Also, just curious. Your emails often end with a nice quote. Does a tool
pick those for you?
Hah, yeah, sort of -- I wrote a ten-line Perl program that chooses one
from a silly line-based formatted text file I came up with for that
purpose; Mutt calls that to choose one for every email. The set of
quotes changes over the years ... I think I first started collecting
such quotes back when I was using FidoNet in 1993 or so, but of course
it has evolved -- some of the old ones becoming cringe-worthy, whatever.
Regards
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Si quieres ser creativo, aprende el arte de perder el tiempo"