sql - 使用串行主键列安全地重命名表

Question

我知道使用 SERIAL 主键的 PostgreSQL 表以 PostgreSQL 创建的隐式索引、序列和约束结束。问题是如何在重命名表时重命名这些隐式对象。下面是我在最后用具体问题解决这个问题的尝试。

给定一个表格，例如:

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

Postgres 输出:

NOTICE: CREATE TABLE will create implicit sequence "foo_pkey_seq" for serial column "foo.pkey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
Query returned successfully with no result in 52 ms.

pgAdmin III SQL Pane 显示了表的以下 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;

Query returned successfully with no result in 17 ms.

pgAdmin 三:

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;

Query returned successfully with no result in 17 ms.

pgAdmin 三:

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。如何重命名约束？

Answer 1

serial 不是实际的数据类型。 The manual states :

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns

通过执行所有这些操作，伪数据类型得到解析:

• 创建一个名为tablename_colname_seq

  的序列

• 创建 integer 类型的列(或 int2/int8 分别用于 smallserial/bigserial)

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

• 让列拥有序列，以便它自动被删除

系统不知道您是手动还是通过伪数据类型serial 完成所有这些操作。 pgAdmin 检查列出的特性，如果满足所有特性，反向工程 DDL 脚本将使用匹配的 serial 类型进行简化。如果不满足其中一个特征，则不会进行此简化。这就是 pgAdmin 所做的事情。对于基础目录表，它都是一样的。没有这样的 serial 类型。

无法自动重命名拥有的序列。你可以运行:

ALTER SEQUENCE ... RENAME TO ...

就像你一样。系统本身并不关心名称。 DEFAULT 列存储一个 OID ('foo_pkey_seq'::regclass)，您可以更改序列的名称而不破坏它 - OID 保持不变。外键和数据库内部的类似引用也是如此。

主键的隐式索引绑定(bind)到 PK 约束的名称，如果您更改表的名称，它不会改变。 In Postgres 9.2 or later you can use

ALTER TABLE ... RENAME CONSTRAINT ..

也要纠正这一点。

也可以有引用表名命名的索引。 Similar procedure :

ALTER INDEX .. RENAME TO  ..

您可以对表名进行各种非正式引用。系统不能强制重命名可以随意命名的对象。它不在乎。

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

Postgres 在重命名对象之前也会获取对象的锁。因此，如果打开的并发事务对相关对象有任何类型的锁定，则您的RENAME 操作将停止，直到这些事务提交或回滚。

系统目录和 OID

数据库模式存储在系统模式 pg_catalog 中的系统目录表中。 All details in the manual here.如果您不确切知道自己在做什么，您根本不应该弄乱这些表格。一个错误的举动，你可以破坏你的数据库。 Use the DDL commands Postgres provides.

Postgres 提供了一些最重要的表格 object identifier types并键入转换以快速获取 OID 的名称，反之亦然。喜欢:

SELECT 'foo_pkey_seq'::regclass

如果架构名称在 search_path 中并且表名称是唯一的，则结果与:

SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';

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