在Postgres中使用串行主键列安全且干净地重命名表？ [英] Safely and cleanly rename tables that use serial primary key columns in Postgres?

问题描述

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

给定一个表，例如

  CREATE TABLE foo（
 pkey SERIAL PRIMARY KEY，
 value INTEGER 
）; 
  

Postgres输出

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

PgAdmin III显示以下表格的DDL

  CREATE TABLE foo 
（
 pkey serial NOT NULL，
 value integer，
 CONSTRAINT foo_pkey PRIMARY KEY（pkey）
）
 WITH（
 OIDS = FALSE 
）; 
 ALTER TABLE foo 
 OWNER TO postgres; 
  

现在重命名表

  ALTER table foo RENAME TO bar; 
  

Postgres输出

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

表格中的PgAdmin III SQL窗格

  CREATE TABLE bar 
（
 pkey integer NOT NULL DEFAULT nextval（'foo_pkey_seq':: regclass），
值整数，
 CONSTRAINT foo_pkey PRIMARY KEY（pkey）
）
 WITH（
 OIDS = FALSE 
）; 
 ALTER TABLE bar 
 OWNER TO postgres; 
  

注意额外的 DEFAULT nextval（'foo_pkey_seq':: regclass ）， 这意味着重命名表不会重命名主键的序列，但是现在我们有这个显式的 nextval（）

现在重命名序列

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

  ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq; 
查询成功返回，结果为17 ms。 
  

查看pgAdmin III中的SQL窗格我看到

  CREATE TABLE bar 
（
 pkey serial NOT NULL，
 value integer，
 CONSTRAINT foo_pkey PRIMARY KEY（pkey）
）
 WITH（
 OIDS = FALSE 
）; 
 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 。如何重新命名一个约束？

解决方案

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

数据类型smallserial，serial和bigserial不是真正的类型，
，而只是创建唯一标识符列的标志性方便

伪数据类型解决了所有这些：

• 创建名为的序列tablename_colname_seq




• 创建类型为 （或 int2 / int8 分别为 smallserial / bigserial ）




• 使列不NULL DEFAULT nextval（'tablename_colname_seq'）




• 使列拥有序列，从而自动删除它。 / p>

系统不会知道您是否手动或通过伪数据类型 serial 。 pgAdmin检查列出的功能，如果所有功能都被满足，逆向工程DDL脚本将使用匹配的串行类型进行简化。如果其中一个功能不能满足，则不会发生这种简化。这是pgAdmin的功能。对于底层目录表，它们都是一样的。没有 serial 类型。

我很积极，没有办法自动重命名所有的序列。您可以运行

  ALTER SEQUENCE ... RENAME TO ... 
  

像你一样。系统本身不关心名称。列 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。

I know that PostgreSQL tables that use a SERIAL primary key end up with an 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.

Given a table such as

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

Postgres outputs

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 shows the following as the DDL for the table

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

Now rename the table

ALTER table foo RENAME TO bar;

Postgres output

Query returned successfully with no result in 17 ms.

PgAdmin III SQL pane for the table

CREATE TABLE bar
(
  pkey integer NOT NULL DEFAULT nextval('foo_pkey_seq'::regclass),
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
)
WITH (
  OIDS=FALSE
);
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().

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.

Looking the SQL Pane in pgAdmin III I see

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

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

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?

解决方案

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

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:

• Create a sequence named tablename_colname_seq

• Create the column with type integer (or int2 / int8 respectively 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.

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.

I am pretty positive 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 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.

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 ..

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.

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.

System catalogs and OIDs

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.

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

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';

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

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