PostgreSQL 10 标识列详解

@

目录

• 原文连接:

原文连接:

https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained

For PostgreSQL 10, I have worked on a feature called “identity columns”. Depesz already wrote a blog post about it and showed that it works pretty much like serial columns:
对于 PostgreSQL 10，我开发了一个名为“标识列”的功能。Depesz 已经写了一篇关于它的博客文章，并展示了它的工作原理与串行列非常相似：

CREATE TABLE test_old (
    id serial PRIMARY KEY,
    payload text
);

INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;

与

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);

INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;

do pretty much the same thing, except that the new way is more verbose.
做的事情几乎相同，只是新方法更加冗长。

So why bother? 那么何必呢？

Compatibility 兼容性
The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some have lately been adopting the standard SQL syntax, however. So now you can move code around between, for example, PostgreSQL, DB2, and Oracle without any change (in this area).
新语法符合 SQL 标准。创建自增列一直是不同 SQL 实现之间臭名昭著的不兼容问题。不过，最近有些 SQL 实现已经采用了标准 SQL 语法。因此，现在您可以在 PostgreSQL、DB2 和 Oracle 等系统之间移动代码，而无需（在这方面）进行任何更改。

Permissions 权限
A general problem with the old way is that the system doesn’t actually remember that the user typed serial. It expands this at parse time into something like
旧方法的一个常见问题是，系统实际上并不记得用户输入了 serial。它会在解析时将其扩展为类似

CREATE SEQUENCE test_old_id_seq;

CREATE TABLE test_old (
    id int NOT NULL PRIMARY KEY,
    payload text
);

ALTER TABLE test_old
    ALTER COLUMN id SET DEFAULT nextval('test_old_id_seq');

ALTER SEQUENCE test_old_id_seq OWNED BY test_old.id;

The OWNED BY in the last command is an attempt to remember something about the serialness, but it is still insufficient in some cases.
最后一个命令中的 OWNED BY 试图记住一些有关序列性的信息，但在某些情况下仍然不够。

The new way creates the sequence as a proper internal dependency of the table, so that various weird implementation details of the serial pseudotype are not exposed.
新方法将序列创建为表的适当内部依赖项，这样就不会暴露序列伪类型的各种奇怪的实现细节。

One common problem is that permissions for the sequence created by a serial column need to be managed separately:
一个常见的问题是，需要单独管理由序列列创建的序列的权限：

CREATE USER foo;
GRANT INSERT ON test_old TO foo;
GRANT INSERT ON test_new TO foo;

SET SESSION AUTHORIZATION foo;

INSERT INTO test_old (payload) VALUES ('d');
ERROR:  permission denied for sequence test_old_id_seq

INSERT INTO test_new (payload) VALUES ('d');
-- OK

You can fix the error by also running
您还可以通过运行

GRANT USAGE ON SEQUENCE test_old_id_seq;

If you have deployment scripts, this is annoying and problematic, because the name of the sequence is automatically generated. Here, of course, it appears in the error message, and it is easy to guess, but sometimes a slightly different name is chosen, and then your deployment scripts will fail.
如果您有部署脚本，这很烦人，也很麻烦，因为序列的名称是自动生成的。当然，它出现在错误消息中，而且很容易猜到，但有时会选择一个稍微不同的名称，然后您的部署脚本就会失败。

Managing sequences 管理序列
You also need to know the name of the sequence if you want to make some changes to the sequence:
如果您想对序列进行一些更改，您还需要知道序列的名称：

ALTER SEQUENCE test_old_id_seq RESTART WITH 1000;

With an identity column, you don’t need to know the name of the sequence:
使用标识列，您不需要知道序列的名称：

ALTER TABLE test_new ALTER COLUMN id RESTART WITH 1000;

Schema management 架构管理
Since serial is not a real type, it can only be used in certain circumstances. You can specify serial as a column type when creating a table or when adding a column. But dropping serialness from an existing column or adding it to an existing column is not straightforward.
由于 serial 并非真实类型，因此只能在特定情况下使用。您可以在创建表或添加列时指定 serial 作为列类型。但是，从现有列中删除 serial 或将其添加到现有列并不简单。

To drop serialness, you can drop the sequence (again, after ascertaining the name) with the CASCADE option, which cascades to remove the default value of the associated column:
要删除序列，可以使用 CASCADE 选项删除序列（再次确定名称后），该选项会级联删除关联列的默认值：

DROP SEQUENCE test_old_id_seq CASCADE;

If you instead drop the default value like
如果你删除默认值，例如

ALTER TABLE test_old ALTER COLUMN id DROP DEFAULT;

it will drop the default but leave the sequence in place.
它将删除默认值但保留序列。

If you want to take an existing integer column and turn it into a serial column, there is no single command to do that. You will have to manually assemble the CREATE SEQUENCE and ALTER TABLE ... SET DEFAULT commands shown earlier.
如果要将现有的整数列转换为串行列，没有单一的命令可以做到这一点。您必须手动组合前面介绍的 CREATE SEQUENCE 和 ALTER TABLE ... SET DEFAULT 命令。

Dropping the identity property of an existing column is easy:
删除现有列的标识属性很容易：

ALTER TABLE test_new ALTER COLUMN id DROP IDENTITY;

You cannot accidentally make a mistake and drop a default, because there is none. But just in case, you get a nice error message:
你不可能不小心犯错，把默认设置删掉，因为根本没有默认设置。不过，为了以防万一，你会收到一条友好的错误信息：

=> ALTER TABLE test_new ALTER COLUMN id DROP DEFAULT;
ERROR:  column "id" of relation "test_new" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.

You can also turn an existing integer column into an identity column with one command:
您还可以使用一个命令将现有的整数列转换为标识列：

ALTER TABLE test_new
    ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

Copying table structures 复制表结构
If you use the CREATE TABLE / LIKE functionality to copy the structure of a table, serial columns pose a problem:
如果使用 CREATE TABLE / LIKE 功能复制表的结构，则串行列会带来问题：

CREATE TABLE test_old2 (LIKE test_old INCLUDING ALL);
INSERT INTO test_old2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  4 | e

Note that even though the new table is a separate table, it keeps using the old sequence.
请注意，即使新表是一个单独的表，它仍会继续使用旧序列。

This gets even more confusing when you want to drop the first table:
当你想删除第一个表时，这会变得更加令人困惑：

=> DROP TABLE test_old;
ERROR:  cannot drop table test_old because other objects depend on it
DETAIL:  default for table test_old2 column id depends on sequence test_old_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

(You can use CASCADE as hinted, or drop test_old2 first. The latter works without CASCADE because the sequence is still linked to the first table.)
（您可以按照提示使用 CASCADE，或者先删除 test_old2。后者无需 CASCADE 即可工作，因为序列仍然链接到第一个表。）

When you copy a table with an identity column in this way, you get a new sequence:
当您以这种方式复制具有标识列的表时，您将获得一个新序列：

CREATE TABLE test_new2 (LIKE test_new INCLUDING ALL);
INSERT INTO test_new2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  1 | e

Upgrading 升级
Perhaps you are convinced and you want to “upgrade” all your messy serial columns to this new identity column thing. (Note that you don’t have to “upgrade”. You can keep using serial columns the same way as before.) Here is a PL/pgSQL function that you can use:
也许你已经被说服了，并且想要把所有杂乱的序列列“升级”成这个新的标识列。（注意，你不必“升级”。你可以像以前一样继续使用序列列。）这里有一个你可以使用的 PL/pgSQL 函数：

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  colnum smallint;
  seqid oid;
  count int;
BEGIN
  -- find column number
  SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'column does not exist';
  END IF;

  -- find sequence
  SELECT INTO seqid objid
    FROM pg_depend
    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
      AND classid = 'pg_class'::regclass AND objsubid = 0
      AND deptype = 'a';

  GET DIAGNOSTICS count = ROW_COUNT;
  IF count < 1 THEN
    RAISE EXCEPTION 'no linked sequence found';
  ELSIF count > 1 THEN
    RAISE EXCEPTION 'more than one linked sequence found';
  END IF;  

  -- drop the default
  EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';

  -- change the dependency between column and sequence to internal
  UPDATE pg_depend
    SET deptype = 'i'
    WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
      AND deptype = 'a';

  -- mark the column as identity column
  UPDATE pg_attribute
    SET attidentity = 'd'
    WHERE attrelid = tbl
      AND attname = col;
END;
$$;

Call it like this: 像这样调用它：

SELECT upgrade_serial_to_identity('test_old', 'id');

If you are using schemas, it would look like this:
如果您使用模式，它将看起来像这样：

SELECT upgrade_serial_to_identity('public.test_old', 'id');

Check your table definition before and after:
检查之前和之后的表定义：

postgres=# \d+ test_old
                                                      Table "public.test_old"
 Column  |  Type   | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id      | integer |           | not null | generated by default as identity | plain    |             |              |
 payload | text    |           |          |                                  | extended |             |              |
Indexes:
    "test_old_pkey" PRIMARY KEY, btree (id)
Access method: heap