使用串行主键列安全地重命名表 [英] Safely rename tables using serial primary key columns

问题描述

我知道使用SERIAL主键的PostgreSQL表最终会由PostgreSQL创建一个隐式索引，序列和约束.问题是在重命名表时如何重命名这些隐式对象.下面是我尝试用特定的问题来解决这个问题.

I know that PostgreSQL tables that use a SERIAL primary 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.

给出一个表格，例如:

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

Postgres输出:

Postgres outputs:

注意:CREATE TABLE将为序列列"foo.pkey"创建隐式序列"foo_pkey_seq"
注意:CREATE TABLE/PRIMARY KEY将为表"foo"创建隐式索引"foo_pkey"
查询成功返回，但在52毫秒内没有结果.

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窗格显示该表的以下DDL脚本(已整理):

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

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毫秒内没有结果.

Query returned successfully with no result in 17 ms.

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;

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

现在重命名序列:

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

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

ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq;

查询成功返回，但在17毫秒内没有结果.

Query returned successfully with no result in 17 ms.

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;

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

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

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

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?

推荐答案

serial不是实际的数据类型. 手册说明:

serial is not an actual data type. The manual states:

数据类型smallserial，serial和bigserial不是真实类型， 但这仅仅是创建唯一标识符列的一种符号上的方便

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

解析伪数据类型可以完成所有这一切:

The pseudo data type is resolved doing all of this:

• 创建一个名为tablename_colname_seq

创建类型为integer的列(对于smallserial/bigserial分别为int2/int8)

create the column with type integer (or int2 / int8 respectively for smallserial / bigserial)

将列设置为NOT NULL DEFAULT nextval('tablename_colname_seq')

使该列拥有序列，以便该列自动被删除

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

系统不不知道您是手动完成还是通过伪数据类型serial完成了所有这些操作. pgAdmin检查列出的功能，如果满足所有条件，则使用匹配的serial类型简化了反向工程DDL脚本.如果不满足其中一项功能，则不会进行这种简化. pgAdmin就是这么做的.对于基础目录表，它们都是相同的.没有这样的serial类型.

The system does not know 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 serial type. 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 serial type as such.

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

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

ALTER SEQUENCE ... RENAME TO ...

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

like you did. The system itself doesn't care about the name. The column DEFAULT stores 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.

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

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

ALTER TABLE ... RENAME CONSTRAINT ..

也要纠正这一点.

也可以有引用表名称命名的索引. 类似过程:

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.

当然，您不想使引用这些名称的SQL代码无效.显然，您不想在应用程序逻辑引用名称时更改名称.通常，对于索引，序列或约束的名称来说，这不会有问题，因为通常不会按名称引用这些索引，序列或约束.

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.

Postgres还会在重命名对象之前获得对它们的锁定.因此，如果打开的并发事务对所讨论的对象有任何锁定，则您的RENAME操作将暂停，直到那些事务提交或回滚为止.

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

数据库架构存储在系统架构pg_catalog中的系统目录的表中. 手册中的所有详细信息.如果您不完全了解自己的意思，这样，您完全不应该弄乱那些表.一个错误的举动，您就可以破坏数据库. 使用Postgres提供的DDL命令.

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.

对于某些最重要的表，Postgres提供了对象标识符类型并键入强制类型转换以快速获取OID的名称，反之亦然.喜欢:

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

SELECT 'foo_pkey_seq'::regclass

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

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

SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';

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

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

这篇关于使用串行主键列安全地重命名表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！