| 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
| 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 |
| 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 |