From 06a04b4ce767e2baaebaf112fe82b18d1be5d9a2 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Wed, 3 Jan 2024 10:00:22 +0530 Subject: [PATCH 09/27] Changing Identity column of a partitioned table The change is propagated to all the partitions. Changing the column only in a partitioned table or a partition is not allowed; the change needs to be applied to the whole partition hierarchy. Ashutosh Bapat --- src/backend/commands/tablecmds.c | 48 +++++++++++++++++++++++--- src/test/regress/expected/identity.out | 28 +++++++++++++++ src/test/regress/sql/identity.sql | 11 ++++++ 3 files changed, 83 insertions(+), 4 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f0c1a85031..67b38c5101 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -455,7 +455,8 @@ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode, bool recurse, bool recursing); static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode); + Node *def, LOCKMODE lockmode, + bool recurse, bool recursing); static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode, bool recurse, bool recursing); @@ -4835,7 +4836,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetIdentity: 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; /* This should run after AddIdentity, so do it in MISC pass */ pass = AT_PASS_MISC; break; @@ -5238,7 +5241,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, cur_pass, context); Assert(cmd != NULL); - address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode); + address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode, + cmd->recurse, false); break; case AT_DropIdentity: address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, @@ -8220,7 +8224,8 @@ ATExecAddIdentity(Relation rel, const char *colName, * Return the address of the affected column. */ static ObjectAddress -ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode) +ATExecSetIdentity(Relation rel, const char *colName, Node *def, + LOCKMODE lockmode, bool recurse, bool recursing) { ListCell *option; DefElem *generatedEl = NULL; @@ -8229,6 +8234,19 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod AttrNumber attnum; Relation attrelation; ObjectAddress address; + bool ispartitioned; + + ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + if (ispartitioned && !recurse) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change identity 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 change identity column of a partition")); foreach(option, castNode(List, def)) { @@ -8293,6 +8311,28 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod heap_freetuple(tuple); table_close(attrelation, RowExclusiveLock); + /* + * Recurse to propagate the identity change to partitions. Identity is not + * inherited in regular inheritance children. + */ + if (generatedEl && 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); + ATExecSetIdentity(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 d2860b4347..e185e0d4a1 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -587,6 +587,34 @@ SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; pitest2_p2 | 08-04-2016 | from pitest2 | 6 (6 rows) +-- SET identity column +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails +ERROR: cannot change identity column of a partition +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails +ERROR: cannot change identity column of a partition +ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails +ERROR: cannot change identity column of only the partitioned table +HINT: Do not specify the ONLY keyword. +ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; +INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200); +INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p1 | 07-02-2016 | from pitest2 | 1 + pitest2_p1 | 07-03-2016 | from pitest2_p1 | 3 + pitest2_p1 | 07-04-2016 | from pitest2 | 5 + pitest2_p1 | 07-05-2016 | from pitest2 | 200 + pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002 + pitest2_p2 | 08-02-2016 | from pitest2 | 2 + pitest2_p2 | 08-03-2016 | from pitest2_p2 | 4 + pitest2_p2 | 08-04-2016 | from pitest2 | 6 + pitest2_p2 | 08-05-2016 | from pitest2 | 1000 + pitest2_p2 | 08-06-2016 | from pitest2_p2 | 300 +(10 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'); diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index 62323f9cbd..a1862df1d8 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -362,6 +362,17 @@ 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; +-- SET identity column +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails +ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails +ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; +INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200); +INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300); +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'); -- 2.25.1