华山论剑之PostgreSQLsequence(上篇)

database

前言

本文是 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 的日常工作,学习实用数据库技术干货!

公众号优质文章推荐

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

以上是 华山论剑之PostgreSQLsequence(上篇) 的全部内容, 来源链接: utcz.com/z/536334.html

回到顶部