I wrote:
> After thinking about this awhile, I feel that we ought to disallow
> it in the traditional-inheritance case as well. The reason is that
> there are semantic prohibitions on inserting or updating a generated
> column, eg
> regression=# create table t (f1 int, f2 int generated always as (f1+1)
> stored);
> CREATE TABLE
> regression=# update t set f2=42;
> ERROR: column "f2" can only be updated to DEFAULT
> DETAIL: Column "f2" is a generated column.
> It's not very reasonable to have to recheck that for child tables,
> and we don't. But if one does this:
> regression=# create table pp (f1 int, f2 int);
> CREATE TABLE
> regression=# create table cc (f1 int, f2 int generated always as (f1+1)
> stored) inherits(pp);
> NOTICE: merging column "f1" with inherited definition
> NOTICE: merging column "f2" with inherited definition
> CREATE TABLE
> regression=# insert into cc values(1);
> INSERT 0 1
> regression=# update pp set f2 = 99 where f1 = 1;
> UPDATE 1
> regression=# table cc;
> f1 | f2
> ----+----
> 1 | 99
> (1 row)
> That is surely just as broken as the partition-based case.
So what we need is about like this. This is definitely not something
to back-patch, since it's taking away what had been a documented
behavior. You could imagine trying to prevent such UPDATEs instead,
but I judge it not worth the trouble. If anyone were actually using
this capability we'd have heard bug reports.
regards, tom lane
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index d91a781479..6b60cd80ae 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -344,8 +344,8 @@ CREATE TABLE people (
</listitem>
<listitem>
<para>
- If a parent column is not a generated column, a child column may be
- defined to be a generated column or not.
+ If a parent column is not a generated column, a child column must
+ not be generated either.
</para>
</listitem>
</itemizedlist>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1db3bd9e2e..72ad6507d7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2931,6 +2931,11 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
* also check that the child column doesn't specify a default
* value or identity, which matches the rules for a single
* column in parse_util.c.
+ *
+ * Conversely, if the parent column is not generated, the
+ * child column can't be either. (We used to allow that, but
+ * it results in being able to override the generation
+ * expression via UPDATEs through the parent.)
*/
if (def->generated)
{
@@ -2951,15 +2956,14 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
errmsg("column \"%s\" inherits from generated column but specifies identity",
def->colname)));
}
-
- /*
- * If the parent column is not generated, then take whatever
- * the child column definition says.
- */
else
{
if (newdef->generated)
- def->generated = newdef->generated;
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
+ errmsg("child column \"%s\" specifies generation expression",
+ def->colname),
+ errhint("A child table column cannot be generated unless its parent column is.")));
}
/* If new def has a default, override previous default */
@@ -15038,13 +15042,18 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel)
attributeName)));
/*
- * If parent column is generated, child column must be, too.
+ * Child column must be generated if and only if parent column is.
*/
if (attribute->attgenerated && !childatt->attgenerated)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("column \"%s\" in child table must be a generated column",
attributeName)));
+ if (childatt->attgenerated && !attribute->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" in child table must not be a generated column",
+ attributeName)));
/*
* Check that both generation expressions match.
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index 1db5f9ed47..3c10dabf6d 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -268,38 +268,17 @@ SELECT * FROM gtest1;
4 | 8
(2 rows)
+-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -- error
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
-\d gtest_normal_child
- Table "public.gtest_normal_child"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
- a | integer | | |
- b | integer | | | generated always as (a * 2) stored
-Inherits: gtest_normal
-
-INSERT INTO gtest_normal (a) VALUES (1);
-INSERT INTO gtest_normal_child (a) VALUES (2);
-SELECT * FROM gtest_normal;
- a | b
----+---
- 1 |
- 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)
-
+ERROR: child column "b" specifies generation expression
+HINT: A child table column cannot be generated unless its parent column is.
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
+ERROR: column "b" in child table must not be a generated column
+DROP TABLE gtest_normal, gtest_normal_child;
-- 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
@@ -702,7 +681,10 @@ CREATE TABLE gtest_child PARTITION OF gtest_parent (
f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR: generated columns are not supported on partitions
-DROP TABLE gtest_parent;
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS (2 * 2) STORED);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+ERROR: column "f3" in child table must not be a generated column
+DROP TABLE gtest_parent, gtest_child;
-- partitioned table
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql
index 39eec40bce..01fc4ed892 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -110,17 +110,12 @@ INSERT INTO gtest1_1 VALUES (4);
SELECT * FROM gtest1_1;
SELECT * FROM gtest1;
+-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal);
-\d gtest_normal_child
-INSERT INTO gtest_normal (a) VALUES (1);
-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;
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
+DROP TABLE gtest_normal, gtest_normal_child;
-- test inheritance mismatches between parent and child
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
@@ -370,7 +365,9 @@ CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RA
CREATE TABLE gtest_child PARTITION OF gtest_parent (
f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
-DROP TABLE gtest_parent;
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS (2 * 2) STORED);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+DROP TABLE gtest_parent, gtest_child;
-- partitioned table
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
