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

Safely rename tables using serial primary key columns

提问by ams

I know that PostgreSQL tables that use a SERIALprimary key end up with an implicit index, sequence and constraint being created by PostgreSQL. The question is how to rename these implicit objects when the table is renamed. Below is my attempt at figuring this out with specific questions at the end.

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

Given a table such as:

给定一个表，例如：

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

Postgres outputs:

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.

注意：CREATE TABLE将为串行列隐序列“foo_pkey_seq”“foo.pkey”
注意：CREATE TABLE / PRIMARY KEY将创建隐式索引“foo_pkey”对表“foo”的
查询成功没有结果在52毫秒返回。

pgAdmin III SQL pane shows the following DDL script for the table (decluttered):

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;

Now rename the table:

现在重命名表：

ALTER table foo RENAME TO bar;

Query returned successfully with no result in 17 ms.

查询成功返回，17 毫秒内没有结果。

pgAdmin III:

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;

Note the extra DEFAULT nextval('foo_pkey_seq'::regclass),this means that renaming the table does not rename the sequence for the primary keys but now we have this explicit nextval().

请注意， DEFAULT nextval('foo_pkey_seq'::regclass),这意味着重命名表不会重命名主键的序列，但现在我们有了这个显式的nextval().

Now rename the sequence:

现在重命名序列：

I want to keep the database naming consistent so I tried:

我想保持数据库命名一致，所以我试过：

ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq;

Query returned successfully with no result in 17 ms.

查询成功返回，17 毫秒内没有结果。

pgAdmin III:

pgAdmin III：

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

The DEFAULT nextval('foo_pkey_seq'::regclass),is gone.

该DEFAULT nextval('foo_pkey_seq'::regclass),不见了。

QUESTIONS

问题

1. Why did the DEFAULT nextval('foo_pkey_seq'::regclass)statement appear and disappear?
2. Is there a way to rename the table and have the primary key sequence renamed at the same time?
3. Is it safe to rename the table then sequence while clients are connected to the database, are there any concurrency issues?
4. How does postgres know which sequence to use? Is there a database trigger being used internally? Is there anything else to rename other than the table and the sequence?
5. What about the implicit index created by a primary key? Should that be renamed? If so, how can that be done?
6. What about the constraint name above? It is still foo_pkey. How is a constraint renamed?

1. 为什么DEFAULT nextval('foo_pkey_seq'::regclass)声明会出现又消失？
2. 有没有办法重命名表并同时重命名主键序列？
3. 在客户端连接到数据库时重命名表然后排序是否安全，是否存在并发问题？
4. postgres 如何知道使用哪个序列？是否有内部使用的数据库触发器？除了表和序列之外，还有什么可以重命名的吗？
5. 主键创建的隐式索引呢？应该改名吗？如果是这样，那怎么办？
6. 上面的约束名称呢？它仍然是foo_pkey。如何重命名约束？

回答by Erwin Brandstetter

serialis not an actual data type. The manual states:

serial不是实际的数据类型。该手册指出：

The data types smallserial, serialand bigserialare not true types, but merely a notational convenience for creating unique identifier columns

数据类型smallserial，serial而bigserial不是真正的类型，而仅仅是用于创建的唯一标识符列一个符号上的便利

The pseudo data type is resolved doing all of this:

伪数据类型通过所有这些来解析：

• create a sequence named tablename_colname_seq

• create the column with type integer(or int2/ int8respectively for smallserial/ bigserial)

• make the column NOT NULL DEFAULT nextval('tablename_colname_seq')

• make the column own the sequence, so that it gets dropped with it automatically

• 创建一个名为的序列 tablename_colname_seq

• 创建具有类型integer（或int2/int8分别为smallserial/ bigserial）的列

• 做柱子 NOT NULL DEFAULT nextval('tablename_colname_seq')

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

The system does notknow whether you did all this by hand or by way of the pseudo data type serial. pgAdmin checks on the listed features and if all are met, the reverse engineered DDL script is simplified with the matching serialtype. If one of the features is not met, this simplification does not take place. That is something pgAdmin does. For the underlying catalog tables it's all the same. There is no serialtype as such.

该系统不会不知道你是否做了这一切通过手工或伪数据类型的方式serial。pgAdmin 检查列出的功能，如果所有功能都满足，则使用匹配serial类型简化反向工程 DDL 脚本。如果不满足其中一个特征，则不会进行这种简化。这就是 pgAdmin 所做的。对于底层目录表，它都是一样的。没有这样的serial类型。

There is no way to automatically rename owned sequences. You can run:

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

ALTER SEQUENCE ... RENAME TO ...

like you did. The system itself doesn't care about the name. The column DEFAULTstores an OID('foo_pkey_seq'::regclass), you can change the name of the sequence without breaking that - the OID stays the same. The same goes for foreign keys and similar references inside the database.

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

The implicit index for the primary key is bound to the name of the PK constraint, which will notchange if you change the name of the table. In Postgres 9.2 or later you can use

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

ALTER TABLE ... RENAME CONSTRAINT ..

to rectify that, too.

也要纠正这一点。

There can also be indexes named in reference to the table name. Similar procedure:

也可以参考表名命名索引。类似的程序：

ALTER INDEX .. RENAME TO  ..

You can have all kinds of informal references to the table name. The system cannot forcibly rename objects that can be named anything you like. And it doesn't care.

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

Of course you don't want to invalidate SQL code that references those names. Obviously, you don't want to change names while application logic references them. Normally this wouldn't be a problem for names of indexes, sequences or constraints, since those are not normally referenced by name.

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

Postgres also acquires a lock on objects before renaming them. So if there are concurrent transactionopen that have any kind of lock on objects in question, your RENAMEoperation is stalled until those transactions commit or roll back.

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

System catalogs and OIDs

系统目录和 OID

The database schema is stored in tables of the system catalog in the system schema pg_catalog. All details in the manual here.If you don't know exactly what you are doing, you shouldn't be messing with those tables at all. One false move and you can break your database. Use the DDL commands Postgres provides.

数据库模式存储在系统模式中系统目录的表中pg_catalog。此处手册中的所有详细信息。如果您不确切地知道自己在做什么，则根本不应该弄乱这些表。一个错误的举动，你就可以破坏你的数据库。使用 Postgres 提供的 DDL 命令。

For some of the most important tables Postgres provides object identifier typesand type casts to get the name for the OID and vice versa quickly. Like:

对于一些最重要的表，Postgres 提供了对象标识符类型和类型转换来快速获取 OID 的名称，反之亦然。喜欢：

SELECT 'foo_pkey_seq'::regclass

If the schema name is in the search_pathand the table name is unique, that gives you the same as:

如果架构名称在 中search_path并且表名称是唯一的，那么您将获得与以下相同的结果：

SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';

The primary key of most catalog tables is oidand internally, most references use OIDs.

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