特色栏目： python 批处理 net编程 Javascript Php Asp Css Html5 Android seo centos

华山论剑之 PostgreSQL sequence (上篇)

来源：互联网收集：自由互联发布时间：2022-05-17

前言本文是 sequence 系列继三大数据库 sequence 之华山论剑 (Oracle PostgreSQL MySQL sequence 十年经验总结) 之后的第二篇，主要分享一下 PostgreSQL 中关于 sequence 的一些经验。测试环境准备以下

前言

本文是 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 扩展创建失败原因调查

上一篇：『现学现忘』Docker相关概念 — 1、云计算概念
下一篇：没有了

华山论剑之 PostgreSQL sequence (上篇)

相关文章