使用序列主键列安全地重命名表格

Question

我知道使用 SERIAL 主键的 PostgreSQL 表会被 PostgreSQL 创建一个隐式索引、序列和约束。问题是当表重命名时如何重命名这些隐式对象。以下是我试图通过具体问题来解决这个问题的尝试。

例如给定下面的表：

CREATE TABLE foo (
    pkey SERIAL PRIMARY KEY,
    value INTEGER
);

Postgres输出:

注意: 创建表将为序列列"foo.pkey"创建隐式序列"foo_pkey_seq"
注意: CREATE TABLE / PRIMARY KEY将为表"foo"创建隐式索引"foo_pkey"
查询成功返回，用时52毫秒。

pgAdmin III SQL面板显示了以下DDL脚本的表格（去除杂物）：

CREATE TABLE foo (
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE foo OWNER TO postgres;

现在重命名表格：

ALTER table foo RENAME TO bar;

查询成功返回，但结果为空，耗时17毫秒。

pgAdmin III：

CREATE TABLE bar (
  pkey integer NOT NULL DEFAULT nextval('foo_pkey_seq'::regclass),
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE bar OWNER TO postgres;

注意多出来的DEFAULT nextval('foo_pkey_seq'::regclass),，这意味着重命名表格并不会同时重命名主键所用的序列，但现在我们有了明确的nextval()。

现在重命名序列：

我想保持数据库命名的一致性，所以我尝试了：

ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq;

查询成功返回，但结果为空，用时17毫秒。

pgAdmin III：

CREATE TABLE bar (
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE bar OWNER TO postgres;

这个语句DEFAULT nextval('foo_pkey_seq'::regclass)已经消失了。

问题

1. 为什么会出现然后又消失了DEFAULT nextval('foo_pkey_seq'::regclass) 这个语句？
2. 是否有一种方式可以重命名表并同时重命名主键序列？
3. 在客户端连接到数据库时，重命名表和序列是否安全？是否存在并发问题？
4. Postgres是如何知道要使用哪个序列的？内部是否使用数据库触发器？除了表和序列之外还需要重命名其他内容吗？
5. 主键隐式创建的索引怎么办？应该重命名吗？如果是，应该如何操作？
6. 上面提到的约束名仍然是foo_pkey。如何重命名约束？

- ams

---

我的猜想是将一个序列命名为<<tablename>>_pkey_seq具有神奇的语法意义，并且Postgres知道使用此命名的序列作为表的主键序列，并省略明确列出它作为表的主键列的默认值。我实际上并不知道这一点，也没有任何证据支持它。如果这个问题到下班时间还没有答案，我会进一步调查。 - Mark Amery

Answer 1

serial 不是一个实际的数据类型。手册说明：

smallserial、serial 和 bigserial 数据类型不是真正的类型，而只是一种方便创建唯一标识符列的表示方式。

伪数据类型通过执行以下所有操作来解析：

• 创建名为 tablename_colname_seq 的序列

• 使用 integer 类型创建该列（对于 smallserial / bigserial 分别使用 int2 / int8）

• 使该列 NOT NULL DEFAULT nextval('tablename_colname_seq')

• 使该列拥有该序列，以便它随之自动删除

系统无法确定您是通过手动操作还是使用伪数据类型serial完成的。pgAdmin会检查列出的功能是否已满足，如果都满足，则反向工程DDL脚本将简化为匹配的serial类型。如果其中一个功能未满足，则不会进行此简化。这是pgAdmin的工作。对于底层目录表而言，它们都是相同的。没有serial类型。

自动重命名所有序列的方法不存在。您可以运行：

ALTER SEQUENCE ... RENAME TO ...

像你一样。系统本身并不关心 {name}。列 {DEFAULT} 存储 OID（'{foo_pkey_seq}'::regclass），你可以更改序列的名称而不会破坏该值——OID保持不变。数据库内部的外键和类似引用也是如此。

主键的隐式索引绑定在PK约束的名称上，如果更改表的名称，它将不会更改。在Postgres 9.2或更高版本中，您可以使用

ALTER TABLE ... RENAME CONSTRAINT ..

请纠正这一点。

还可以使用以表名命名的索引。 类似的步骤：

ALTER INDEX .. RENAME TO  ..

您可以随意引用表名，系统无法强制重命名可以随意命名的对象。而且它并不关心。

当然，您不希望使引用这些名称的SQL代码失效。显然，您不希望在应用程序逻辑引用它们时更改名称。通常，这对于索引、序列或约束的名称来说不是问题，因为这些名称通常不会被引用。

Postgres在重命名对象之前会获取对象的锁。因此，如果有任何类型的锁定正在进行中，您的RENAME操作将被阻塞，直到这些事务提交或回滚。

系统目录和OID

数据库模式存储在系统模式pg_catalog的系统目录表中。手册中提供了所有详细信息。如果您不确定自己在做什么，最好不要去碰那些表。一旦出现问题，您的数据库可能会受到破坏。请使用Postgres提供的DDL命令。

Postgres提供对象标识符类型和类型转换来快速获取OID的名称以及反之。例如，在一些最重要的表中：

SELECT 'foo_pkey_seq'::regclass

如果模式名称在 search_path 中，而表名是唯一的，则这与以下内容相同：

SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';

大多数目录表的主键是oid，在内部，大多数引用使用OID。

- Erwin Brandstetter

---

"ALTER TABLE ... RENAME CONSTRAINT .." 需要 PostgreSQL 9.2+。也许这对我刚刚调试了一个小时很有帮助 :-P 很棒的答案！ - Simon Steinberger