详解数仓对象设计中序列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 20
OWNED 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)用于递增序列并返回新值。

返回类型：bigint

nextval函数有两种调用方式（其中第二种调用方式兼容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()函数将不支持下推。

返回类型：bigint

currval函数有两种调用方式（其中第二种调用方式兼容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 text
postgres(# );
 
根据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过程中遇到的常见问题及解决方法进行了汇总。

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