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


Reply via email to