华山论剑之 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 扩展创建失败原因调查
原文地址：https://www.cnblogs.com/dbadaily/archive/2022/03/07/pg-sequence-1.html
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/44.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
