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

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: shihao zhong <zhong950419(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, Manu <manuelreyesbravo(at)gmail(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>, Richard Guo <guofenglinux(at)gmail(dot)com>
Subject: Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Date: 2026-11-15 21:15:36
Message-ID: CAN4CZFOfJPujTUekbH=O7mUjwOTTknaBtKZck5k6CFPUAaf-ug@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Zsolt Parragi 2026-11-15 21:22:59 Re: Re: Assertion failure in WAL summarizer
Previous Message Jim Jones 2026-11-15 19:45:56 Re: CREATE SCHEMA still resolves unqualified type names in pg_temp

Browse pgsql-committers by date

  From Date Subject
Previous Message shihao zhong 2026-11-15 03:36:13 Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned