Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEztM-0003Rr-Im for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 21:41:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEztJ-0006nN-KJ for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 21:41:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEztJ-0006nE-2V for pgsql-hackers@lists.postgresql.org; Mon, 09 Jan 2023 21:41:13 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEztE-00005j-6M for pgsql-hackers@lists.postgresql.org; Mon, 09 Jan 2023 21:41:11 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 309Lf5pV044979; Mon, 9 Jan 2023 16:41:05 -0500 From: Tom Lane To: Amit Langote cc: pgsql-hackers@lists.postgresql.org, Peter Eisentraut Subject: Re: ATTACH PARTITION seems to ignore column generation status In-reply-to: <3016779.1673026372@sss.pgh.pa.us> References: <2793383.1672944799@sss.pgh.pa.us> <3016779.1673026372@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 06 Jan 2023 12:32:52 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <44889.1673300408.0@sss.pgh.pa.us> Date: Mon, 09 Jan 2023 16:41:05 -0500 Message-ID: <44978.1673300465@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <44889.1673300408.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable 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=3D# create table t (f1 int, f2 int generated always as (f1+1)= stored); > CREATE TABLE > regression=3D# update t set f2=3D42; > 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=3D# create table pp (f1 int, f2 int); > CREATE TABLE > regression=3D# 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=3D# insert into cc values(1); > INSERT 0 1 > regression=3D# update pp set f2 =3D 99 where f1 =3D 1; > UPDATE 1 > regression=3D# 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="disallow-generated-child-columns-2.patch"; charset="us-ascii" Content-ID: <44889.1673300408.2@sss.pgh.pa.us> Content-Description: disallow-generated-child-columns-2.patch Content-Transfer-Encoding: quoted-printable 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 ( - 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. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/table= cmds.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 re= lpersistence, * 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 r= elpersistence, errmsg("column \"%s\" inherits from generated column but specifi= es identity", def->colname))); } - - /* - * If the parent column is not generated, then take whatever - * the child column definition says. - */ else { if (newdef->generated) - def->generated =3D 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 par= ent 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 colum= n", + attributeName))); = /* * Check that both generation expressions match. diff --git a/src/test/regress/expected/generated.out b/src/test/regress/ex= pected/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) st= ored -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) IN= HERITS (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 ('2= 016-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 GENERAT= ED 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/gen= erated.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) IN= HERITS (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 ('2= 016-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 GENERAT= ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1); ------- =_aaaaaaaaaa0--