详解数仓对象设计中序列 SEQUENCE 原理与应用

本文分享自华为云社区《GaussDB(DWS)对象设计之序列SEQUENCE原理与使用方法介绍》，作者：VV 一笑。

1. 前言

• 适用版本：8.2.1 及以上版本

序列 SEQUENCE 用来生成唯一整数的数据库对象，本文对序列 SEQUENCE 的使用场景、使用方法及相关函数进行了介绍，并针对序列 SEQUENCE 在使用中容易遇到的问题和对应的解决方法进行了梳理总结。

2. SEQUENCE——自增整数序列

序列 Sequence 是用来产生唯一整数的数据库对象。序列的值是按照一定规则自增的整数。因为自增所以不重复，因此说 Sequence 具有唯一标识性。因此，在数据库中 Sequence 常常被作为主键使用。

3. 创建序列

通过序列使某字段成为唯一标识符的方法有两种：

• 是声明字段的类型为序列整型，由数据库在后台自动创建一个对应的 Sequence。

• 使用 CREATE SEQUENCE 自定义一个新的 Sequence，然后将 nextval(‘sequence_name’)函数读取的序列值，指定为某一字段的默认值，这样该字段就可以作为唯一标识符。

方法一： 声明字段类型为序列整型来定义标识符字段。例如：

postgres=# CREATE TABLE T1(    id    serial,    name  text);

方法二： 创建序列，并通过 nextval(‘sequence_name’)函数指定为某一字段的默认值。这种方式更灵活，可以为序列定义 cache，一次预申请多个序列值，减少与 GTM 的交互次数，来提高性能。

1.创建序列

postgres=# CREATE SEQUENCE seq1 cache 100;

2.指定为某一字段的默认值，使该字段具有唯一标识属性。

postgres=# CREATE TABLE T2 (     id   int not null default nextval('seq1'),    name text);

【注意】

除了为序列指定了 cache，方法二所实现的功能基本与方法一类似。但是一旦定义 cache，序列将会产生空洞(序列值为不连贯的数值，如：1.4.5)，并且不能保序。另外为某序列指定从属列后，该列删除，对应的 sequence 也会被删除。 虽然数据库并不限制序列只能为一列产生默认值，但最好不要多列共用同一个序列。当前版本只支持在定义表的时候指定自增列，或者指定某列的默认值为 nextval(‘seqname’)， 不支持在已有表中增加自增列或者增加默认值为 nextval(‘seqname’)的列。

3.1 CREATE SEQUENCE 语句的使用方法

CREATE SEQUENCE 用于向当前数据库里增加一个新的序列。序列的 Owner 为创建此序列的用户。

注意事项

• Sequence 是一个存放等差数列的特殊表，该表受 DBMS 控制。这个表没有实际意义，通常用于为行或者表生成唯一的标识符。

• 如果给出一个模式名，则该序列就在给定的模式中创建，否则会在当前模式中创建。序列名必须和同一个模式中的其他序列、表、索引、视图或外表的名字不同。

• 创建序列后，在表中使用序列的 nextval()函数和 generate_series(1,N)函数对表插入数据，请保证 nextval 的可调用次数大于等于 N+1 次，否则会因为 generate_series()函数会调用 N+1 次而导致报错。

• 不支持在 template1 数据库中创建 SEQUENCE。

语法格式

CREATE SEQUENCE name [ INCREMENT [ BY ] increment ]    [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE]     [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE ]     [ OWNED BY { table_name.column_name | NONE } ];

参数说明

• name

将要创建的序列名称。

取值范围: 仅可以使用小写字母（a~z）、 大写字母（A~Z），数字和特殊字符"#"，"_"，"$"的组合。

• increment

指定序列的步长。一个正数将生成一个递增的序列，一个负数将生成一个递减的序列。

缺省值为 1。

• MINVALUE minvalue | NO MINVALUE| NOMINVALUE

执行序列的最小值。如果没有声明 minvalue 或者声明了 NO MINVALUE，则递增序列的缺省值为 1，递减序列的缺省值为-263-1。NOMINVALUE 等价于 NO MINVALUE

• MAXVALUE maxvalue | NO MAXVALUE| NOMAXVALUE

执行序列的最大值。如果没有声明 maxvalue 或者声明了 NO MAXVALUE，则递增序列的缺省值为 263-1，递减序列的缺省值为-1。NOMAXVALUE 等价于 NO MAXVALUE

• start

指定序列的起始值。缺省值：对于递增序列为 minvalue，递减序列为 maxvalue。

• cache

为了快速访问，而在内存中预先存储序列号的个数。一个缓存周期内，CN 不再向 GTM 索取序列号，而是使用本地预先申请的序列号。

缺省值为 1，表示一次只能生成一个值，也就是没有缓存。

【注意】

◾不建议同时定义 cache 和 maxvalue 或 minvalue。因为定义 cache 后不能保证序列的连续性，可能会产生空洞，造成序列号段浪费。

◾建议 cache 值不要设置过大，否则会出现缓存序列号时（每个 cache 周期的第一个 nextval）耗时过长的情况；同时建议 cache 值小于 100000000。实际使用时应根据业务设置合理的 cache 值，既能保证快速访问，又不会浪费序列号。

• CYCLE

用于使序列达到 maxvalue 或者 minvalue 后可循环并继续下去。

如果声明了 NO CYCLE，则在序列达到其最大值后任何对 nextval 的调用都会返回一个错误。

NOCYCLE 的作用等价于 NO CYCLE。

缺省值为 NO CYCLE。

若定义序列为 CYCLE，则不能保证序列的唯一性。

• OWNED BY-

将序列和一个表的指定字段进行关联。这样，在删除那个字段或其所在表的时候会自动删除已关联的序列。关联的表和序列的所有者必须是同一个用户，并且在同一个模式中。需要注意的是，通过指定 OWNED BY，仅仅是建立了表的对应列和 sequence 之间关联关系，并不会在插入数据时在该列上产生自增序列。

缺省值为 OWNED BY NONE，表示不存在这样的关联。

【注意】

◾通过 OWNED BY 创建的 Sequence 不建议用于其他表，如果希望多个表共享 Sequence，该 Sequence 不应该从属于特定表。

示例

创建一个从 101 开始的递增序列，名为 serial：

CREATE SEQUENCE serial START 101 CACHE 20;

从序列中选出下一个数字：

SELECT nextval('serial');  nextval  ---------    101

从序列中选出下一个自增数字：

SELECT nextval('serial');  nextval  ---------    102

创建与表关联的序列：

CREATE TABLE customer_address(    ca_address_sk             integer               not null,    ca_address_id             char(16)              not null,    ca_street_number          char(10)                      ,    ca_street_name            varchar(60)                   ,    ca_street_type            char(15)                      ,    ca_suite_number           char(10)                      ,    ca_city                   varchar(60)                   ,    ca_county                 varchar(30)                   ,    ca_state                  char(2)                       ,    ca_zip                    char(10)                      ,    ca_country                varchar(20)                   ,    ca_gmt_offset             decimal(5,2)                  ,    ca_location_type          char(20)                     ) ; CREATE SEQUENCE serial1 START 101 CACHE 20OWNED BY customer_address.ca_address_sk;

使用 serial 创建主键自增序列表 serial_table：

CREATE TABLE serial_table(a int, b serial);INSERT INTO serial_table (a) VALUES (1),(2),(3);SELECT * FROM serial_table ORDER BY b; a | b---+--- 1 | 1 2 | 2 3 | 3(3 rows)

4. 修改序列

ALTER SEQUENCE 命令更改现有序列的属性，包括修改修改拥有者、归属列和最大值。

指定序列与列的归属关系：将序列和一个表的指定字段进行关联。在删除那个字段或其所在表的时候会自动删除已关联的序列。

postgres=# ALTER SEQUENCE seq1 OWNED BY T2.id;

将序列 serial 的最大值修改为 300：

ALTER SEQUENCE seq1 MAXVALUE 300;

4.1 ALTER SEQUENCE 语句的使用方法

ALTER SEQUENCE 用于修改一个现有的序列的参数。

注意事项

• 使用 ALTER SEQUENCE 的用户必须是该序列的所有者。

• 当前版本仅支持修改拥有者、归属列和最大值。若要修改其他参数，可以删除重建，并用 Setval 函数恢复当前值。

• ALTER SEQUENCE MAXVALUE 不支持在事务、函数和存储过程中使用。

• 修改序列的最大值后，会清空该序列在所有会话的 cache。

• ALTER SEQUENCE 会阻塞 nextval、setval、currval 和 lastval 的调用。

语法格式

修改序列最大值或归属列

ALTER SEQUENCE [ IF EXISTS ] name     [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE ]    [ OWNED BY { table_name.column_name | NONE } ] ;

修改序列的拥有者

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

参数说明

• name

将要修改的序列名称。

• IF EXISTS

当序列不存在时使用该选项不会出现错误消息，仅有一个通知。

• MAXVALUE maxvalue | NO MAXVALUE

序列所能达到的最大值。如果声明了 NO MAXVALUE，则递增序列的缺省值为 263-1，递减序列的缺省值为-1。NOMAXVALUE 等价于 NO MAXVALUE。

• OWNED BY

将序列和一个表的指定字段进行关联。这样，在删除那个字段或其所在表的时候会自动删除已关联的序列。

如果序列已经和表有关联后，使用这个选项后新的关联关系会覆盖旧的关联。

关联的表和序列的所有者必须是同一个用户，并且在同一个模式中。

使用 OWNED BY NONE 将删除任何已经存在的关联。

• new_owner

序列新所有者的用户名。用户要修改序列的所有者，必须是新角色的直接或者间接成员，并且那个角色必须有序列所在模式上的 CREATE 权限。

示例

将序列 serial 的最大值修改为 200：

ALTER SEQUENCE serial MAXVALUE 200;

创建一个表,定义默认值：

CREATE TABLE T1(C1 bigint default nextval('serial'));

将序列 serial 的归属列变为 T1.C1：

ALTER SEQUENCE serial OWNED BY T1.C1;

5. 删除序列

使用 DROP SEQUENCE 命令删除一个序列。 例如，将删除名为 seq1 的序列：

DROP SEQUENCE seq1;

5.1 DROP SEQUENCE 语句的使用方法

DROP SEQUENCE 用于从当前数据库里删除序列。

注意事项

只有序列的所有者或者系统管理员才能删除。

语法格式

DROP SEQUENCE [ IF EXISTS ] {[schema.]sequence_name} [ , ... ] [ CASCADE | RESTRICT ];

参数说明

• IF EXISTS

如果指定的序列不存在，则发出一个 notice 而不是抛出一个错误。

• name

序列名称。

• CASCADE

级联删除依赖序列的对象。

• RESTRICT

如果存在任何依赖的对象，则拒绝删除序列。此项是缺省值。

6. SEQUENCE 相关函数

序列函数为用户从序列对象中获取后续的序列值提供了简单的多用户安全的方法。DWS 目前支持以下 SEQUENCE 函数：

6.1 nextval(regclass)

nextval(regclass)用于递增序列并返回新值。返回类型：bigintnextval 函数有两种调用方式（其中第二种调用方式兼容 Oracle 的语法，目前不支持 Sequence 命名中有特殊字符"."的情况），调用方式如下：

示例 1：

postgres=# SELECT nextval('seqDemo');  nextval---------    2(1 row)

示例 2：

postgres=# SELECT seqDemo.nextval;  nextval---------    2(1 row)

注意事项

为了避免从同一个序列获取值的并发事务被阻塞， nextval 操作不会回滚；也就是说，一旦一个值已经被抓取， 那么就认为它已经被用过了，并且不会再被返回。 即使该操作处于事务中，当事务之后中断，或者如果调用查询结束不使用该值，也是如此。这种情况将在指定值的顺序中留下未使用的"空洞"。 因此，GaussDB(DWS)序列对象不能用于获得"无间隙"序列。

如果 nextval 被下推到 DN 上时，各个 DN 会自动连接 GTM，请求 next values 值，例如（insert into t1 select xxx，t1 某一列需要调用 nextval 函数），由于 GTM 上有最大连接数为 8192 的限制，而这类下推语句会导致消耗过多的 GTM 连接数，因此对于这类语句的并发数目限制为 7000（其它语句需要占用部分连接）/集群 DN 数目。

6.2 currval(regclass)

currval(regclass)用于返回当前会话里最近一次 nextval 返回的指定的 sequence 的数值。如果当前会话还没有调用过指定的 sequence 的 nextval，那么调用 currval 将会报错。需要注意的是，这个函数在默认情况下是不支持的，需要通过设置 enable_beta_features 为 true 之后，才能使用这个函数。同时在设置 enable_beta_features 为 true 之后，nextval()函数将不支持下推。返回类型：bigintcurrval 函数有两种调用方式（其中第二种调用方式兼容 Oracle 的语法，目前不支持 Sequence 命名中有特殊字符"."的情况），调用方式如下：

示例 1：

postgres=# SELECT currval('seq1');  currval---------    2(1 row)

示例 2：

postgres=# SELECT seq1.currval seq1;  currval---------    2(1 row)

6.3 lastval()

lastval()用于返回当前会话里最近一次 nextval 返回的数值。这个函数等效于 currval，只是它不用序列名为参数，它抓取当前会话里面最近一次 nextval 使用的序列。如果当前会话还没有调用过 nextval，那么调用 lastval 将会报错。

需要注意的是，lastval()函数在默认情况下是不支持的，需要通过设置 enable_beta_features 或者 lastval_supported 为 true 之后，才能使用这个函数。同时这种情况下，nextval()函数将不支持下推。

返回类型：bigint

示例：

postgres=# SELECT lastval();  lastval---------    2(1 row)

6.4 setval(regclass, bigint)

setval(regclass, bigint)用于设置序列的当前数值。

返回类型：bigint

示例：

postgres=# SELECT setval('seqDemo',1); setval--------    1(1 row)

6.5 setval(regclass, bigint, boolean)

setval(regclass, bigint, boolean)用于设置序列的当前数值以及 is_called 标志。

返回类型：bigint

示例：

postgres=# SELECT setval('seqDemo',1,true); setval--------    1(1 row)

注意事项

Setval 后当前会话及 GTM 上会立刻生效，但如果其他会话有缓存的序列值，只能等到缓存值用尽才能感知 Setval 的作用。所以为了避免序列值冲突，setval 要谨慎使用。因为序列是非事务的，setval 造成的改变不会由于事务的回滚而撤销。

7. 注意事项

新序列值的产生是靠 GTM 维护的，默认情况下，每申请一个序列值都要向 GTM 发送一次申请，GTM 在当前值的基础上加上步长值作为产生的新值返回给调用者。GTM 作为全局唯一的节点，势必成为性能的瓶颈，所以对于需要大量频繁产生序列号的操作，如使用 Bulkload 工具进行数据导入场景，是非常不推荐产生默认序列值的。比如，在下面所示的场景中， INSERT FROM SELECT 语句的性能会非常慢。

CREATE SEQUENCE newSeq1;CREATE TABLE newT1           (              id   int not null default nextval('newSeq1'),              name text            );INSERT INTO newT1(name) SELECT name from T1;

可以提高性能的写法是（假设 T1 表导入 newT1 表中的数据为 10000 行）：

INSERT INTO newT1(id, name) SELECT id,name from T1;SELECT SETVAL('newSeq1',10000);

序列操作函数 nextval()，setval() 等均不支持回滚。另外 setval 设置的新值，会对当前会话的 nextval 立即生效，但对其他会话，如果定义了 cache，不会立即生效，在用尽所有缓存的值后，其变动才被其他会话感知。所以为了避免产生重复值，要谨慎使用 setval，设置的新值不能是已经产生的值或者在缓存中的值。

如果必须要在 bulkload 场景下产生默认序列值，则一定要为 newSeq1 定义足够大的 cache，并且不要定义 Maxvalue 或者 Minvalue。数据库会试图将 nextval(‘sequence_name’)的调用下推到 Data Node，以提高性能。 目前 GTM 对并发的连接请求是有限制的，当 Data Node 很多时，将产生大量并发连接， 这时一定要控制 bulkload 的并发数目，避免耗尽 GTM 的连接资源。如果目标表为复制表(DISTRIBUTE BY REPLICATION)时下推将不能进行。当数据量较大时，这对数据库将是个灾难。除了性能问题之外，空间也可能会剧烈膨胀，在导入结束后，需要用 vacuum full 来恢复。最好的方式还是如上建议的，不要在 bulkload 的场景中产生默认序列值。

另外，序列创建后，在每个节点上都维护了一张单行表，存储序列的定义及当前值，但此当前值并非 GTM 上的当前值，只是保存本节点与 GTM 交互后的状态。如果其他节点也向 GTM 申请了新值，或者调用了 Setval 修改了序列的状态，不会刷新本节点的单行表，但因每次申请序列值是向 GTM 申请，所以对序列正确性没有影响。

8. 使用案例

DWS 如何重置自增列的开始序号？

使用函数 setval(regclass, bigint)对自增列值进行重置。

示例：

将seqDemo列的开始序号重置为1： postgres=# SELECT setval('seqDemo',1); setval--------    1(1 row)

DWS 如何确定 sequence 和哪个表有关联？

先在 pg_class 查找目标 sequence 的 oid，然后在 pg_depend 根据 oid 查依赖该 sequence 的对象

示例：

先创建自增序列seq1和依赖seq1的表T2： postgres=# CREATE SEQUENCE seq1 cache 100; postgres=# CREATE TABLE T2 postgres-# ( postgres(#     id   int not null default nextval('seq1'),postgres(#     name textpostgres(# ); 根据seq1从表pg_class、pg_depend联合查询到依赖表T2的oid： postgres=# select * from pg_depend where objid = (select oid from pg_class where relname = 'seq1') classid |   objid    | objsubid | refclassid | refobjid | refobjsubid | deptype ---------+------------+----------+------------+----------+-------------+---------    1259 | 2147485853 |        0 |       2615 |     2200 |           0 | n(1 row)

如何查询序列的 last_value？

由于 SEQUENCE 在自增过程中并不是严格逐个增加，因此序列号中会存在空端数据，所以 last_value 本身并没有实际意义，可以采用函数 lastval()进行查询。

示例：

postgres=# SELECT lastval();  lastval---------    2(1 row)

注意事项

如果当前会话还没有调用过 nextval，那么调用 lastval 将会报错。此外，lastval()函数在默认情况下是不支持的，需要通过设置 enable_beta_features 或者 lastval_supported 为 true 之后，才能使用这个函数。同时这种情况下，nextval()函数将不支持下推。

如何查询 SEQUENC 的当前最新值？

通过 currval 函数可以查询 SEQUENC 的当前最新值。

示例：

currval函数有两种调用方式（其中第二种调用方式兼容Oracle的语法，目前不支持Sequence命名中有特殊字符"."的情况），调用方式如下：示例1：postgres=# SELECT currval('seq1');  currval---------    2(1 row) 示例2：postgres=# SELECT seq1.currval seq1;  currval---------    2(1 row)

如何解决 SEQUENC 取值超出范围的问题？

1.可以在创建 SEQUENC 时设置 CYCLE 字段，从而使得序列达到 maxvalue 或者 minvalue 后可循环并继续下去。但需要注意，若定义序列为 CYCLE，则不能保证序列的唯一性。

2.通过调用 setval(regclass, bigint)函数对序列取值进行重置。

9. 总结

本文介绍了 SEQUENCE 的使用场景和相关的函数的使用方法，并对使用 SEQUENCE 过程中遇到的常见问题及解决方法进行了汇总。

点击关注，第一时间了解华为云新鲜技术~