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