本文分享自華為雲社群《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;
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;
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'));
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命名中有特殊字元"."的情況），呼叫方式如下：
postgres=# SELECT nextval('seqDemo'); nextval --------- 2 (1 row)
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命名中有特殊字元"."的情況），呼叫方式如下：
postgres=# SELECT currval('seq1'); currval --------- 2 (1 row)
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過程中遇到的常見問題及解決方法進行了彙總。
點選關注，第一時間瞭解華為雲新鮮技術~