On 2021-01-29 17:41, Tom Lane wrote:
Also, in the example from [2],
d3=# create table pp1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE TABLE
d3=# create table cc1 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
CREATE TABLE
d3=# alter table cc1 inherit pp1;
ALTER TABLE
pg_dump now omits to dump cc1's generation expression, which seems
strictly worse than before. Admittedly, the backend likely ought to
be rejecting this scenario, but it doesn't do so today.
[2]https://www.postgresql.org/message-id/661371.1601398006%40sss.pgh.pa.us
Here is a WIP patch to address this. Probably needs another look for
column number mapping and all the usual stuff, but the basic idea should
be okay.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From bd6ee462e5ad4a6514940e7e78d40c4f28f3dc3f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 3 Feb 2021 20:14:05 +0100
Subject: [PATCH] WIP: 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.
---
src/backend/commands/tablecmds.c | 49 +++++++++++++++++++++++++
src/test/regress/expected/generated.out | 19 ++++++++++
src/test/regress/sql/generated.sql | 13 +++++++
3 files changed, 81 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 420991e315..3d4fb4ce7e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13963,6 +13963,55 @@ MergeAttributesIntoExisting(Relation child_rel,
Relation parent_rel)
errmsg("column \"%s\" in child
table must be marked NOT NULL",
attributeName)));
+ /*
+ * If parent column 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.
+ */
+ if (attribute->attgenerated && childatt->attgenerated)
+ {
+ TupleConstr *child_constr =
child_rel->rd_att->constr;
+ TupleConstr *parent_constr =
parent_rel->rd_att->constr;
+ Node *child_expr = NULL;
+ Node *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 =
stringToNode(child_constr->defval[i].adbin);
+ 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 =
stringToNode(parent_constr->defval[i].adbin);
+ break;
+ }
+ }
+ Assert(parent_expr != NULL);
+
+ if (!equal(child_expr, parent_expr))
+ 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..f2ed3864a1 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,14 @@ 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
-- 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..e30dfe649d 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -113,11 +113,24 @@ 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
+
+
-- test multiple inheritance mismatches
CREATE TABLE gtesty (x int, b int);
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
--
2.30.0
