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 TABLETime: 158.910 ms[local]:5432 pg12@testdb=# CREATE TABLE t_serial (pg12@testdb(#     id serial PRIMARY KEY,pg12@testdb(#     c1 varcharpg12@testdb(# );CREATE TABLETime: 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 3Time: 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 textpg12@testdb(# );CREATE TABLETime: 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 3Time: 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, skippingDROP ROLETime: 0.422 ms[local]:5432 pg12@testdb=# CREATE USER user1029 with password 'test';CREATE ROLETime: 0.543 ms[local]:5432 pg12@testdb=# GRANT INSERT ON t_serial TO user1029;GRANTTime: 1.297 ms[local]:5432 pg12@testdb=# GRANT INSERT ON t_identify TO user1029;GRANTTime: 3.729 ms[local]:5432 pg12@testdb=# SET SESSION AUTHORIZATION user1029;SETTime: 1.243 ms[local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d');ERROR:  permission denied for sequence t_serial_id_seqTime: 2.705 ms[local]:5432 user1029@testdb=> INSERT INTO t_identify (c1) VALUES ('d');INSERT 0 1Time: 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;GRANTTime: 5.291 ms[local]:5432 pg12@testdb=# -- user1029[local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d');INSERT 0 1Time: 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 itDETAIL:  default value for column id of table t_serial depends on sequence t_serial_id_seqHINT:  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_serialDROP SEQUENCETime: 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 serialcolumns.  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