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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
