PostgreSQL DBA(117) - pgAdmin(Don't do this： Don't use serial)

no zuo no die系列，来自于pg的wiki。
这一节的内容是：不要使用serial。
理由是:

The serial types have some weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome.

该类型有某些行为会给模式、依赖和权限管理带来不必要的麻烦。

基本用法

[local]:5432 pg12@testdb=# drop table if exists t_serial;
DROP TABLE
Time: 158.910 ms
[local]:5432 pg12@testdb=# CREATE TABLE t_serial (
pg12@testdb(#     id serial PRIMARY KEY,
pg12@testdb(#     c1 varchar
pg12@testdb(# );
CREATE TABLE
Time: 9.424 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# INSERT INTO t_serial (c1) VALUES ('a'), ('b'), ('c') RETURNING *;
 id | c1 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)
INSERT 0 3
Time: 3.076 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select * from t_serial;
 id | c1 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)
Time: 0.847 ms
[local]:5432 pg12@testdb=#

serial与GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY的作用很相似

[local]:5432 pg12@testdb=# CREATE TABLE t_identify (
pg12@testdb(#     id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
pg12@testdb(#     c1 text
pg12@testdb(# );
CREATE TABLE
Time: 5.215 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# INSERT INTO t_identify (c1) VALUES ('a'), ('b'), ('c') RETURNING *;
 id | c1 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)
INSERT 0 3
Time: 1.127 ms
[local]:5432 pg12@testdb=#

实际上，serial符合SQL标准具备兼容性，而GENERATED BY DEFAULT AS IDENTITY是PG的语法不具备兼容性。

权限
serial类型的第一个问题是与serial列相关的sequence需要单独处理

[local]:5432 pg12@testdb=# drop user if exists user1029;
NOTICE:  role "user1029" does not exist, skipping
DROP ROLE
Time: 0.422 ms
[local]:5432 pg12@testdb=# CREATE USER user1029 with password 'test';
CREATE ROLE
Time: 0.543 ms
[local]:5432 pg12@testdb=# GRANT INSERT ON t_serial TO user1029;
GRANT
Time: 1.297 ms
[local]:5432 pg12@testdb=# GRANT INSERT ON t_identify TO user1029;
GRANT
Time: 3.729 ms
[local]:5432 pg12@testdb=# SET SESSION AUTHORIZATION user1029;
SET
Time: 1.243 ms
[local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d');
ERROR:  permission denied for sequence t_serial_id_seq
Time: 2.705 ms
[local]:5432 user1029@testdb=> INSERT INTO t_identify (c1) VALUES ('d');
INSERT 0 1
Time: 3.340 ms
[local]:5432 user1029@testdb=>

可以看到，类型serial的实现底层依赖于sequence，id列对应的sequence是t_serial_id_seq。
而GENERATED BY DEFAULT AS IDENTITY则不需要依赖，因此执行不会出错。
通过授权可以解决此问题

-- pg12
[local]:5432 pg12@testdb=# GRANT USAGE ON SEQUENCE t_serial_id_seq to user1029;
GRANT
Time: 5.291 ms
[local]:5432 pg12@testdb=# 
-- user1029
[local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d');
INSERT 0 1
Time: 3.791 ms
[local]:5432 user1029@testdb=>

由于serial类型依赖于sequence，如果我们对sequence进行相关操作，那会出现什么情况？

[local]:5432 pg12@testdb=# drop sequence t_serial_id_seq;
ERROR:  cannot drop sequence t_serial_id_seq because other objects depend on it
DETAIL:  default value for column id of table t_serial depends on sequence t_serial_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 1.056 ms

存在依赖，删除时会报错，添加cascade选项。

[local]:5432 pg12@testdb=# drop sequence t_serial_id_seq cascade; 
NOTICE:  drop cascades to default value for column id of table t_serial
DROP SEQUENCE
Time: 10.075 ms
[local]:5432 pg12@testdb=# \d t_serial
                   Table "public.t_serial"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           | not null | 
 c1     | character varying |           |          | 
Indexes:
    "t_serial_pkey" PRIMARY KEY, btree (id)
[local]:5432 pg12@testdb=#

t_serial列变成了普通的int字段。

虽然有些不足，但还是可以用的

Identity columns
 This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:
- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

参考资料
Don’t Do This
PostgreSQL 10 identity columns explained