On 05.02.21 15:18, Peter Eisentraut wrote:
Anyway, I figured out how to take account of generation expressions with different column orders. I used the same approach that we use for check constraints. The attached patch is good to go from my perspective.
Dusting this off ... this patch should go into the next minor releases. The attached patch is for master but backpatches without manual intervention to PG13 and PG12.
From 5a24ea0dc89c82a84efe59ef82557b8cd017def6 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pe...@eisentraut.org> Date: Mon, 26 Apr 2021 13:54:34 +0200 Subject: [PATCH v3] Fix ALTER TABLE / INHERIT with generated columns When running ALTER TABLE t2 INHERIT t1, we must check that columns in t2 that correspond to a generated column in t1 are also generated and have the same generation expression. Otherwise, this would allow creating setups that a normal CREATE TABLE sequence would not allow. Discussion: https://www.postgresql.org/message-id/22de27f6-7096-8d96-4619-7b882932c...@2ndquadrant.com --- src/backend/commands/tablecmds.c | 60 +++++++++++++++++++++++++ src/test/regress/expected/generated.out | 21 +++++++++ src/test/regress/sql/generated.sql | 14 ++++++ 3 files changed, 95 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 7d00f4eb25..1b7a4282bf 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -14398,6 +14398,66 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) errmsg("column \"%s\" in child table must be marked NOT NULL", attributeName))); + /* + * If parent column is generated, child column must be, too. + */ + if (attribute->attgenerated && !childatt->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in child table must be a generated column", + attributeName))); + + /* + * Check that both generation expressions match. + * + * The test we apply is to see whether they reverse-compile to the + * same source string. This insulates us from issues like whether + * attributes have the same physical column numbers in parent and + * child relations. (See also constraints_equivalent().) + */ + if (attribute->attgenerated && childatt->attgenerated) + { + TupleConstr *child_constr = child_rel->rd_att->constr; + TupleConstr *parent_constr = parent_rel->rd_att->constr; + char *child_expr = NULL; + char *parent_expr = NULL; + + Assert(child_constr != NULL); + Assert(parent_constr != NULL); + + for (int i = 0; i < child_constr->num_defval; i++) + { + if (child_constr->defval[i].adnum == childatt->attnum) + { + child_expr = + TextDatumGetCString(DirectFunctionCall2(pg_get_expr, + CStringGetTextDatum(child_constr->defval[i].adbin), + ObjectIdGetDatum(child_rel->rd_id))); + break; + } + } + Assert(child_expr != NULL); + + for (int i = 0; i < parent_constr->num_defval; i++) + { + if (parent_constr->defval[i].adnum == attribute->attnum) + { + parent_expr = + TextDatumGetCString(DirectFunctionCall2(pg_get_expr, + CStringGetTextDatum(parent_constr->defval[i].adbin), + ObjectIdGetDatum(parent_rel->rd_id))); + break; + } + } + Assert(parent_expr != NULL); + + if (strcmp(child_expr, parent_expr) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in child table has a conflicting generation expression", + attributeName))); + } + /* * OK, bump the child column's inheritance count. (If we fail * later on, this change will just roll back.) diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index ca721d38bf..675773f0c1 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -281,6 +281,17 @@ SELECT * FROM gtest_normal; 2 | 4 (2 rows) +CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); +ALTER TABLE gtest_normal_child2 INHERIT gtest_normal; +INSERT INTO gtest_normal_child2 (a) VALUES (3); +SELECT * FROM gtest_normal; + a | b +---+--- + 1 | + 2 | 4 + 3 | 9 +(3 rows) + -- test inheritance mismatches between parent and child CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error NOTICE: merging column "b" with inherited definition @@ -292,6 +303,16 @@ ERROR: column "b" inherits from generated column but specifies default CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error NOTICE: merging column "b" with inherited definition ERROR: column "b" inherits from generated column but specifies identity +CREATE TABLE gtestxx_1 (a int NOT NULL, b int); +ALTER TABLE gtestxx_1 INHERIT gtest1; -- error +ERROR: column "b" in child table must be a generated column +CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED); +ALTER TABLE gtestxx_2 INHERIT gtest1; -- error +ERROR: column "b" in child table has a conflicting generation expression +CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED); +ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok +CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL); +ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok -- test multiple inheritance mismatches CREATE TABLE gtesty (x int, b int); CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index bd2b0bfaaa..63251c443a 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -113,11 +113,25 @@ CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED INSERT INTO gtest_normal_child (a) VALUES (2); SELECT * FROM gtest_normal; +CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); +ALTER TABLE gtest_normal_child2 INHERIT gtest_normal; +INSERT INTO gtest_normal_child2 (a) VALUES (3); +SELECT * FROM gtest_normal; + -- test inheritance mismatches between parent and child CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error +CREATE TABLE gtestxx_1 (a int NOT NULL, b int); +ALTER TABLE gtestxx_1 INHERIT gtest1; -- error +CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED); +ALTER TABLE gtestxx_2 INHERIT gtest1; -- error +CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED); +ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok +CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL); +ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok + -- test multiple inheritance mismatches CREATE TABLE gtesty (x int, b int); CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error base-commit: 6d2e87a077b3c2394e4adb8eb226b3dcfe3f3346 -- 2.31.1
