From 106fee38ca410855098ce3acb2d8d9092da13b64 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Mon, 11 Dec 2023 16:41:09 +0530 Subject: [PATCH 07/27] Adding identity to partitioned table adds it to all partitions ... recursively. Additionally do not allow adding identity to only partitioned table or a partition Ashutosh Bapat --- src/backend/commands/tablecmds.c | 47 +++++++++++++++++++++++--- src/test/regress/expected/identity.out | 30 ++++++++++++++++ src/test/regress/sql/identity.sql | 20 +++++++++++ 3 files changed, 93 insertions(+), 4 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 527a106c48..5ae0739377 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -452,7 +452,8 @@ static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName, static ObjectAddress ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, Node *newDefault); static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode); + Node *def, LOCKMODE lockmode, + bool recurse, bool recursing); static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode); static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode); @@ -4825,7 +4826,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_AddIdentity: ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); - /* This command never recurses */ + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; pass = AT_PASS_ADD_OTHERCONSTR; break; case AT_SetIdentity: @@ -5224,7 +5227,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, cur_pass, context); Assert(cmd != NULL); - address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode); + address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode, + cmd->recurse, false); break; case AT_SetIdentity: cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, @@ -8105,7 +8109,7 @@ ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, */ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode) + Node *def, LOCKMODE lockmode, bool recurse, bool recursing) { Relation attrelation; HeapTuple tuple; @@ -8113,6 +8117,19 @@ ATExecAddIdentity(Relation rel, const char *colName, AttrNumber attnum; ObjectAddress address; ColumnDef *cdef = castNode(ColumnDef, def); + bool ispartitioned; + + ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + if (ispartitioned && !recurse) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot add identity to a column of only the partitioned table"), + errhint("Do not specify the ONLY keyword."))); + + if (rel->rd_rel->relispartition && !recursing) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot add identity to a column of a partition")); attrelation = table_open(AttributeRelationId, RowExclusiveLock); @@ -8167,6 +8184,28 @@ ATExecAddIdentity(Relation rel, const char *colName, table_close(attrelation, RowExclusiveLock); + /* + * Recurse to propagate the identity column to partitions. Identity is not + * inherited in regular inheritance children. + */ + if (recurse && ispartitioned) + { + List *children; + ListCell *lc; + + children = find_inheritance_children(RelationGetRelid(rel), + lockmode); + + foreach(lc, children) + { + Relation childrel; + + childrel = table_open(lfirst_oid(lc), NoLock); + ATExecAddIdentity(childrel, colName, def, lockmode, recurse, true); + table_close(childrel, NoLock); + } + } + return address; } diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 93d9a60b03..9de35470af 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -587,6 +587,36 @@ SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; pitest2_p2 | 08-04-2016 | from pitest2 | 6 (6 rows) +-- changing a regular column to identity column in a partitioned table +CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); +CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1); +INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2); +-- fails, changing only a partition not allowed +ALTER TABLE pitest3_p1 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ERROR: cannot add identity to a column of a partition +-- fails, changing only the partitioned table not allowed +ALTER TABLE ONLY pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ERROR: constraint must be added to child tables too +HINT: Do not specify the ONLY keyword. +ALTER TABLE pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+---- + pitest3_p1 | 07-02-2016 | from pitest3 | 1 + pitest3_p1 | 07-03-2016 | from pitest3_p1 | 2 + pitest3_p1 | 07-04-2016 | from pitest3 | 3 + pitest3_p1 | 07-05-2016 | from pitest3_p1 | 4 +(4 rows) + -- partition with identity column of its own is not allowed CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); CREATE TABLE itest_child PARTITION OF itest_parent ( diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index ba655002c0..9d1effa059 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -362,6 +362,26 @@ INSERT into pitest2(f1, f2) VALUES ('2016-07-4', 'from pitest2'); INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-4', 'from pitest2'); SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; +-- changing a regular column to identity column in a partitioned table +CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); +CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1); +INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2); +-- fails, changing only a partition not allowed +ALTER TABLE pitest3_p1 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +-- fails, changing only the partitioned table not allowed +ALTER TABLE ONLY pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ALTER TABLE pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + -- partition with identity column of its own is not allowed CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); CREATE TABLE itest_child PARTITION OF itest_parent ( -- 2.25.1