> 虚幻大学 > 华山论剑之 PostgreSQL sequence (一)

华山论剑之 PostgreSQL sequence (一)

虚幻大学 xuhss 505℃ 0评论
文章目录 显示

Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

https://edu.csdn.net/course/detail/35475

前言

本文是 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 的作用是:

  1. 创建 sequence 时指定类型，使列与 sequence 的类型保持一致
  2. 关联表的列与 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 |

可以看到，

  1. 列 tb_test_sequence.test_id 与 sequence 的类型均为 integer
  2. 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 做了什么事情：

  1. 根据规则 tablename_colname_seq 创建 sequence，并设置 DEFAULT
  2. 增加 NOT NULL 约束
  3. 关联列与 sequence，使表或关联的列 drop 时，关联的 sequence 也会被 drop 掉

注：这里 SERIAL 和 PRIMARY KEY 之一都会默认增加 NOT NULL 约束

用 SERIAL 的确省了不少事，但它有什么问题吗？使用它会不会又引入了新的问题？

  1. SERIAL 对应的数据类型是 integer，作为主键的数据类型，integer 足够吗？
  2. 关联列与 sequence 后，drop 时是方便了，但同时会不会给运维带来新的问题？比如 rename 表，列或 sequence？
  3. 在复制表或迁移表时，又该对 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 的日常工作，学习实用数据库技术干货！
51eadbf8f4bbc290e97e8128d03beba4 - 华山论剑之 PostgreSQL sequence (一)

公众号优质文章推荐

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

转载请注明：xuhss » 华山论剑之 PostgreSQL sequence (一)

喜欢 (0)

您必须 登录 才能发表评论！