Hi, Here is an unfinished patch to implement something which appears on the TODO list under ALTER: automatic renaming of sequences created with serial when the table and column names change. I've often wanted this feature and it seemed like a good starter project. I'd be grateful for any feedback and advice on how I could get it into acceptable shape. Example:
hack=# create table foo (id serial primary key); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE hack=# alter table foo rename to bar; NOTICE: ALTER TABLE will rename implicit sequence "foo_id_seq" to "bar_id_seq" ALTER TABLE hack=# alter table bar rename id to snacks; NOTICE: ALTER TABLE will rename implicit sequence "bar_id_seq" to "bar_snacks_seq" ALTER TABLE Sequences are considered to be renameable if they are owned by the table, and have a name conforming to the name pattern used by CREATE TABLE (table_column_seq with optional trailing numbers). If you've manually renamed a SEQUENCE so that it doesn't conform, it won't touch it. If you've created a SEQUENCE and declared it to be OWNED BY the table, then it will be renamed only if it happens to conform. I'm not sure what to do about permissions. I guess it should silently skip renaming sequences if the user doesn't have appropriate privileges. Useful? Why would anyone not want this behaviour? Have I used inappropriate locking levels? What should I read to understand the rules of locking? Have I failed to handle errors? Have I made memory ownership mistakes? Thanks! Thomas Munro
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 335bdc6..cd6318e 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -876,6 +876,160 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla heap_close(relation, NoLock); } +/* + * Check if name appears to be a sequence name as generated by CREATE TABLE for + * serial columns. column_name may be NULL to mean any column. + */ +static bool +matchesGeneratedSequenceForm(const char *name, + const char *table_name, + const char *column_name) +{ + /* Must start with the table name and an underscore. */ + size_t table_name_len = strlen(table_name); + if (strncmp(name, table_name, table_name_len) != 0) + return false; + name += table_name_len; + if (name[0] != '_') + return false; + name += 1; + if (column_name != NULL) + { + /* Must match a specific column name. */ + size_t column_name_len = strlen(column_name); + if (strncmp(name, column_name, column_name_len) != 0) + return false; + name += column_name_len; + } + else + { + /* Step over any column name. */ + name = strrchr(name, '_'); + if (name == NULL) + return false; + } + /* Must have a trailing 'seq'. */ + if (strncmp(name, "_seq", 4) != 0) + return false; + name += 4; + /* We tolerate any number of digits at the end */ + while (*name) + { + if (*name < '0' || *name > '9') + { + return false; + } + ++name; + } + return true; +} + +/* + * Given a sequence name as generated for serial columns, attempt to extract + * the column name as a newly allocated string. If it can't be done, return + * NULL. + */ +static char * +extractColumnName(const char *seq_name, const char *table_name) +{ + const char *column_begin = seq_name + strlen(table_name) + 1; + const char *column_end = strrchr(column_begin, '_'); + if (column_end == NULL) + { + return NULL; + } + else + { + size_t column_len = column_end - column_begin; + char *column = palloc(column_len + 1); + strcpy(column, column_begin); + column[column_len] = 0; + return column; + } +} + +/* + * Expand a RenameStmt into a list of statements. If the statement renames a + * table or a column, then this builds statements to rename any owned sequences + * that have a name apparently created implicitly. + */ +List * +transformRenameStmt(RenameStmt *stmt) +{ + List *result = NIL; + if (stmt->renameType == OBJECT_TABLE || stmt->renameType == OBJECT_COLUMN) + { + Oid table_relid; + Relation table_rel; + List *sequences; + ListCell *cell; + char *table_name; + + /* Get the table's (current) name. */ + table_relid = RangeVarGetRelid(stmt->relation, NoLock, false); + table_rel = relation_open(table_relid, AccessExclusiveLock); + table_name = RelationGetRelationName(table_rel); + relation_close(table_rel, NoLock); + + /* + * Find all owned sequences, and consider renaming them if they appear + * to conform to the naming rule used for implicit sequence generation + * during table creation. We skip other owned sequences. + */ + sequences = getOwnedSequences(table_relid); + foreach(cell, sequences) + { + Oid seq_relid = lfirst_oid(cell); + Relation seq_rel = relation_open(seq_relid, AccessExclusiveLock); + char *seq_name = RelationGetRelationName(seq_rel); /* pstrdup? */ + Oid seq_namespace_relid = RelationGetNamespace(seq_rel); + char *seq_namespace_name = get_namespace_name(seq_namespace_relid); /* pstrdup? */ + relation_close(seq_rel, NoLock); + + /* + * For table renames, we want to rename sequences for any columns + * (hence the NULL); for column rename we only want sequences named + * after that column of the table. + */ + if (matchesGeneratedSequenceForm(seq_name, + table_name, + stmt->renameType == OBJECT_TABLE ? NULL : stmt->subname)) + { + char *new_table_name = stmt->renameType == OBJECT_TABLE + ? stmt->newname + : table_name; + char *new_column_name = stmt->renameType == OBJECT_TABLE + ? extractColumnName(seq_name, table_name) + : stmt->newname; + char *new_name; + RenameStmt *rename_seq; + /* Choose a new conforming and unique name. */ + new_name = ChooseRelationName(new_table_name, + new_column_name, + "seq", + seq_namespace_relid); + ereport(NOTICE, + (errmsg("ALTER TABLE will rename implicit sequence \"%s\" to \"%s\"", + seq_name, + new_name))); + rename_seq = makeNode(RenameStmt); + rename_seq->object = NIL; + rename_seq->objarg = NIL; + rename_seq->subname = 0; + rename_seq->renameType = OBJECT_SEQUENCE; + rename_seq->relation = makeRangeVar(seq_namespace_name, + seq_name, + -1); + rename_seq->newname = new_name; + result = lcons(rename_seq, result); + } + } + } + + /* The table rename itself is first in the output */ + return lcons(stmt, result); +} + static void transformOfType(CreateStmtContext *cxt, TypeName *ofTypename) { diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index de16a61..3e1930b 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -686,7 +686,32 @@ standard_ProcessUtility(Node *parsetree, * schema */ case T_RenameStmt: - ExecRenameStmt((RenameStmt *) parsetree); + { + List *stmts; + ListCell *l; + stmts = transformRenameStmt((RenameStmt *) parsetree); + foreach(l, stmts) + { + Node *stmt = (Node *) lfirst(l); + if (IsA(stmt, RenameStmt)) + { + ExecRenameStmt((RenameStmt *) stmt); + } + else + { + /* Recurse */ + ProcessUtility(stmt, + queryString, + params, + false, + None_Receiver, + NULL); + } + /* Need CCI between commands */ + if (lnext(l) != NULL) + CommandCounterIncrement(); + } + } break; case T_AlterObjectSchemaStmt: diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h index 4ad793a..13987d1 100644 --- a/src/include/parser/parse_utilcmd.h +++ b/src/include/parser/parse_utilcmd.h @@ -24,5 +24,6 @@ extern IndexStmt *transformIndexStmt(IndexStmt *stmt, const char *queryString); extern void transformRuleStmt(RuleStmt *stmt, const char *queryString, List **actions, Node **whereClause); extern List *transformCreateSchemaStmt(CreateSchemaStmt *stmt); +extern List *transformRenameStmt(RenameStmt *stmt); #endif /* PARSE_UTILCMD_H */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers