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](https://wiki.postgresql.org/wiki/Don't_Do_This)
[PostgreSQL 10 identity columns explained](https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/)

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

相關文章