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

no zuo no die系列，來自于pg的wiki。
這一節(jié)的內(nèi)容是：不要使用serial。
理由是:

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

該類型有某些行為會給模式、依賴和權(quán)限管理帶來不必要的麻煩。

基本用法

[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=#

實(shí)際上，serial符合SQL標(biāo)準(zhǔn)具備兼容性，而GENERATED BY DEFAULT AS IDENTITY是PG的語法不具備兼容性。

權(quán)限
serial類型的第一個(gè)問題是與serial列相關(guān)的sequence需要單獨(dú)處理

[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的實(shí)現(xiàn)底層依賴于sequence，id列對應(yīng)的sequence是t_serial_id_seq。
而GENERATED BY DEFAULT AS IDENTITY則不需要依賴，因此執(zhí)行不會出錯(cuò)。
通過授權(quán)可以解決此問題

-- 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進(jìn)行相關(guān)操作，那會出現(xiàn)什么情況？

[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

存在依賴，刪除時(shí)會報(bào)錯(cuò)，添加cascade選項(xiàng)。

[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