From: Tom Lane <tgl@sss.pgh.pa.us>
To: Amit Langote <amitlangote09@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: ATTACH PARTITION seems to ignore column generation status
Date: Mon, 09 Jan 2023 16:41:05 -0500
Message-ID: <44978.1673300465@sss.pgh.pa.us> (raw)
In-Reply-To: <3016779.1673026372@sss.pgh.pa.us>
References: <2793383.1672944799@sss.pgh.pa.us>
<CA+HiwqEDfKy6znR_AHvH8CYgjxSo19rX3DH-3e0rgb0qsuGW_w@mail.gmail.com>
<3016779.1673026372@sss.pgh.pa.us>
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
Attachments:disallow-generated-child-columns-2.patch (text/x-diff, 7.7K) downloaddiff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgmlindex 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.cindex 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.outindex 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.sqlindex 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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, amitlangote09@gmail.com, peter.eisentraut@enterprisedb.com
Subject: Re: ATTACH PARTITION seems to ignore column generation status
In-Reply-To: <44978.1673300465@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox