Re: ATTACH PARTITION seems to ignore column generation status

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);