前言

本文是 sequence 系列繼三大資料庫 sequence 之華山論劍 (Oracle PostgreSQL MySQL sequence 十年經驗總結) 之后的第二篇，主要分享一下 PostgreSQL 中關于 sequence 的一些經驗，

測驗環境準備

以下測驗是在 PostgreSQL 11 中進行，

通過以下 SQL 創建：

測驗用戶: alvin，普通用戶，非 superuser

測驗資料庫: alvindb，owner 是 alvin

測驗 schema: alvin，owner 也是 alvin

這里采用的是 user 與 schema 同名，結合默認的 search_path("$user", public)，這樣操作物件(table, sequence, etc.)時就不需要加 schema 前綴了，

postgres=# CREATE USER alvin WITH PASSWORD 'alvin';
CREATE ROLE
postgres=# CREATE DATABASE alvindb OWNER alvin;
CREATE DATABASE
postgres=# \c alvindb
You are now connected to database "alvindb" as user "postgres".
alvindb=# CREATE SCHEMA alvin AUTHORIZATION alvin;
CREATE SCHEMA
alvindb=# \c alvindb alvin
You are now connected to database "alvindb" as user "alvin".
alvindb=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

創建 sequence 的兩種方式

sequence 常規用途是用作主鍵序列的生成，下面通過通過創建 sequence 及表來討論 sequence 創建方式，

創建 sequence 方式一直接創建

下面是一種簡單方式直接創建 sequence 及表，

alvindb=> CREATE SEQUENCE tb_test_sequence_test_id_seq;
CREATE SEQUENCE
alvindb=> 
CREATE TABLE tb_test_sequence (
    test_id INTEGER DEFAULT nextval('alvin.tb_test_sequence_test_id_seq') PRIMARY KEY,
    create_time TIMESTAMP DEFAULT clock_timestamp()
);
CREATE TABLE

查看已創建的物件

alvindb=> \d
                    List of relations
 Schema |             Name             |   Type   | Owner 
--------+------------------------------+----------+-------
 alvin  | tb_test_sequence_test_id_seq | sequence | alvin
 alvin  | tb_test_sequence             | table    | alvin
(2 rows)

查看已創建物件的結構

alvindb=> \d tb_test_sequence
                                            Table "alvin.tb_test_sequence"
   Column    |            Type             | Collation | Nullable |                      Default                      
-------------+-----------------------------+-----------+----------+---------------------------------------------------
 test_id     | integer                     |           | not null | nextval('tb_test_sequence_test_id_seq'::regclass)
 create_time | timestamp without time zone |           |          | clock_timestamp()
Indexes:
    "tb_test_sequence_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_sequence_test_id_seq
                Sequence "alvin.tb_test_sequence_test_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

alvindb=>

此時，我們會注意到，問題一，列 tb_test_sequence.test_id 的型別是 integer，而創建的 sequence 默認型別是 bigint，

這樣沒有問題，但如果型別一致的話會更好，

接下來，我們 drop sequence 的話，會發現，由于表依賴 sequence，所以不能單獨 drop sequence，

alvindb=> DROP SEQUENCE tb_test_sequence_test_id_seq;
ERROR:  cannot drop sequence tb_test_sequence_test_id_seq because other objects depend on it
DETAIL:  default value for column test_id of table tb_test_sequence depends on sequence tb_test_sequence_test_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
alvindb=>

下面我們 drop 掉表 tb_test_sequence，

alvindb=> DROP TABLE tb_test_sequence;
DROP TABLE
alvindb=> \d
                    List of relations
 Schema |             Name             |   Type   | Owner 
--------+------------------------------+----------+-------
 alvin  | tb_test_sequence_test_id_seq | sequence | alvin
(1 row)

可以看到，問題二，雖然表 drop 了，但 sequence 還在，

這樣會有什么問題呢？

在一個大型的資料庫系統中，我們可能會發現有好多孤立的 sequence，因為我們 drop 表時可能會忘記 drop 掉其對應的 sequence，

現在先手動 drop 掉 sequence，

alvindb=> DROP SEQUENCE tb_test_sequence_test_id_seq;
DROP SEQUENCE
alvindb=> \d
Did not find any relations.
alvindb=>

我們優化一下 SQL 來解決上述兩個問題：

alvindb=> CREATE SEQUENCE tb_test_sequence_test_id_seq AS INTEGER;
CREATE SEQUENCE
alvindb=> 
CREATE TABLE tb_test_sequence (
    test_id INTEGER DEFAULT nextval('alvin.tb_test_sequence_test_id_seq') PRIMARY KEY,
    create_time TIMESTAMP DEFAULT clock_timestamp()
);
CREATE TABLE
alvindb=> ALTER SEQUENCE tb_test_sequence_test_id_seq OWNED BY tb_test_sequence.test_id;
ALTER SEQUENCE

上述 SQL 的作用是:

創建 sequence 時指定型別，使列與 sequence 的型別保持一致
關聯表的列與 sequence，使 drop 表或列時會自動 drop 與其關聯的 sequence

查看表結構，

alvindb=> \d tb_test_sequence
                                            Table "alvin.tb_test_sequence"
   Column    |            Type             | Collation | Nullable |                      Default                      
-------------+-----------------------------+-----------+----------+---------------------------------------------------
 test_id     | integer                     |           | not null | nextval('tb_test_sequence_test_id_seq'::regclass)
 create_time | timestamp without time zone |           |          | clock_timestamp()
Indexes:
    "tb_test_sequence_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_sequence_test_id_seq
            Sequence "alvin.tb_test_sequence_test_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: alvin.tb_test_sequence.test_id

可以看到，

列 tb_test_sequence.test_id 與 sequence 的型別均為 integer
sequence 下方多了 'Owned by'，表示列與 sequence 已關聯了，

下面 drop 表后，可以看到，sequence 也已被 drop 了，

alvindb=> DROP TABLE tb_test_sequence;
DROP TABLE
alvindb=> \d
Did not find any relations.

實際上，如果 drop 掉列 test_id，其關聯的 sequence 也會被 drop，

alvindb=> ALTER TABLE tb_test_sequence DROP COLUMN test_id;
ALTER TABLE
alvindb=> \d tb_test_sequence
                            Table "alvin.tb_test_sequence"
   Column    |            Type             | Collation | Nullable |      Default      
-------------+-----------------------------+-----------+----------+-------------------
 create_time | timestamp without time zone |           |          | clock_timestamp()
 alvindb=> \d
             List of relations
 Schema |       Name       | Type  | Owner 
--------+------------------+-------+-------
 alvin  | tb_test_sequence | table | alvin
(1 row)

創建 sequence 方式二通過 serial 創建

下面通過一個 SQL 來實作與上面完全相同的效果，

alvindb=> 
CREATE TABLE tb_test_sequence (
    test_id SERIAL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT clock_timestamp()
);
CREATE TABLE

查看表結構，與方式一中完全一樣，

alvindb=> \d
                    List of relations
 Schema |             Name             |   Type   | Owner 
--------+------------------------------+----------+-------
 alvin  | tb_test_sequence             | table    | alvin
 alvin  | tb_test_sequence_test_id_seq | sequence | alvin
(2 rows)
alvindb=> \d tb_test_sequence
                                            Table "alvin.tb_test_sequence"
   Column    |            Type             | Collation | Nullable |                      Default                      
-------------+-----------------------------+-----------+----------+---------------------------------------------------
 test_id     | integer                     |           | not null | nextval('tb_test_sequence_test_id_seq'::regclass)
 create_time | timestamp without time zone |           |          | clock_timestamp()
Indexes:
    "tb_test_sequence_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_sequence_test_id_seq
            Sequence "alvin.tb_test_sequence_test_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: alvin.tb_test_sequence.test_id

這里總結一下一個單詞 SERIAL 做了什么事情：

根據規則 tablename_colname_seq 創建 sequence，并設定 DEFAULT
增加 NOT NULL 約束
關聯列與 sequence，使表或關聯的列 drop 時，關聯的 sequence 也會被 drop 掉

注：這里 SERIAL 和 PRIMARY KEY 之一都會默認增加 NOT NULL 約束

用 SERIAL 的確省了不少事，但它有什么問題嗎？使用它會不會又引入了新的問題？

SERIAL 對應的資料型別是 integer，作為主鍵的資料型別，integer 足夠嗎？
關聯列與 sequence 后，drop 時是方便了，但同時會不會給運維帶來新的問題？比如 rename 表，列或 sequence？
在復制表或遷移表時，又該對 sequence 作何操作呢？

接下來，我們從這幾個問題出發進一步探討，

serial 與 bigserial

serial 對應的是 integer，是 4 個位元組，最大值是 2 147 483 647，即 21 億左右，

作為大表主鍵的 sequence，21 億真的夠嗎？按全球人口 70 億算，一人一個數都不夠，

為解決這個問題，可以用 bigserial，即 bigint，8 個位元組，最大值是 9 223 372 036 854 775 807，即 922億個億左右，這對于絕大多數場景是足夠了，這也是 PostgreSQL 中 sequence 的最大值，

使用 bigserial 創建表：

alvindb=> 
CREATE TABLE tb_test_bigserial (
    test_id BIGSERIAL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT clock_timestamp()
);
CREATE TABLE

查看表結構，

alvindb=> \d tb_test_bigserial
                                            Table "alvin.tb_test_bigserial"
   Column    |            Type             | Collation | Nullable |                      Default                       
-------------+-----------------------------+-----------+----------+----------------------------------------------------
 test_id     | bigint                      |           | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)
 create_time | timestamp without time zone |           |          | clock_timestamp()
Indexes:
    "tb_test_bigserial_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_bigserial_test_id_seq
                Sequence "alvin.tb_test_bigserial_test_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: alvin.tb_test_bigserial.test_id

可以看到，列 test_id 和 sequence 的 Type 都是 bigint，這樣，sequence 的型別問題就解決了，

公眾號

關注 DBA Daily 公眾號，第一時間收到文章的更新，
通過一線 DBA 的日常作業，學習實用資料庫技術干貨！

公眾號優質文章推薦

PostgreSQL VACUUM 之深入淺出

華山論劍之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失敗原因調查

PG 資料匯入 Hive 亂碼問題調查

PostGIS 擴展創建失敗原因調查

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/439191.html

標籤：PostgreSQL

上一篇：oracle 11g 體系結構概述

下一篇：華山論劍之 PostgreSQL sequence (一)

華山論劍之 PostgreSQL sequence (一)

前言