Postgresql Sequence 的操作及容量

接續昨天的討論.先來建立一個 sequence , 設定 maxvalue 為 7, 開始值為 2,
遞增值為 2,會循環.

create sequence nana.s3
as smallint
increment by 2
maxvalue 7
start with 2
cycle;

檢查相關資訊
\d nana.s3
                          Sequence "nana.s3"
+----------+-------+---------+---------+-----------+---------+-------+
|   Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache |
+----------+-------+---------+---------+-----------+---------+-------+
| smallint |     2 |       1 |       7 |         2 | yes     |     1 |
+----------+-------+---------+---------+-----------+---------+-------+

取值
select array_agg(n::text || '->' || nval::text)
  from (select n, nextval('nana.s3') nval
          from generate_series(1, 10) as gs(n)) a;

+------------------------------------------------------+
|                      array_agg                       |
+------------------------------------------------------+
| {1->2,2->4,3->6,4->1,5->3,6->5,7->7,8->1,9->3,10->5} |
+------------------------------------------------------+

利用 array 以及 array_agg()聚合, 這樣避免一長串.
可以看到取了10次,是循環的.

select currval('nana.s3');
+---------+
| currval |
+---------+
|       5 |
+---------+

接著建立 increment 為 -1 的.

create sequence nana.s4
as smallint
increment by -1;

\d nana.s4
                          Sequence "nana.s4"
+----------+-------+---------+---------+-----------+---------+-------+
|   Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache |
+----------+-------+---------+---------+-----------+---------+-------+
| smallint |    -1 |  -32768 |      -1 |        -1 | no      |     1 |
+----------+-------+---------+---------+-----------+---------+-------+

可以觀察到 從 -1 開始, 而且 default 的 Maximum 也是 -1.

來看取值

select array_agg(n::text || '->' || nval::text)
  from (select n, nextval('nana.s4') nval
          from generate_series(1, 5) as gs(n)) a;

+---------------------------------+
|            array_agg            |
+---------------------------------+
| {1->-1,2->-2,3->-3,4->-4,5->-5} |
+---------------------------------+

接著來建立 increment 為 -1, Maximum 為 32767的.

create sequence nana.s5
as smallint
increment by -1
maxvalue 32767;

\d nana.s5
                          Sequence "nana.s5"
+----------+-------+---------+---------+-----------+---------+-------+
|   Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache |
+----------+-------+---------+---------+-----------+---------+-------+
| smallint | 32767 |  -32768 |   32767 |        -1 | no      |     1 |
+----------+-------+---------+---------+-----------+---------+-------+

由上面幾個 sequence 的情況觀察,可以知道.當未設定 Maximum 或 Minimum 時.
以 increment 之正負,來定. 0 是被排除的.
                     -   +
      型態的Min <==(-1) 0 1==> 型態的Max

除了 default 的方式以外,還可以自行設定 Max, Min 來決定取值的範圍.

試試看 Min > Max

create sequence nana.s6
as smallint
minvalue 5
maxvalue 3;

ERROR:  22023: MINVALUE (5) must be less than MAXVALUE (3)

這樣會出現錯誤.

修改一下.

create sequence nana.s6
as smallint
minvalue 3
maxvalue 5;

select array_agg(n::text || '->' || nval::text)
  from (select n, nextval('nana.s6') nval
          from generate_series(1, 3) as gs(n)) a;
+------------------+
|    array_agg     |
+------------------+
| {1->3,2->4,3->5} |
+------------------+

再取一個值.

select nextval('nana.s6');

ERROR:  2200H: nextval: reached maximum value of sequence "s6" (5)

會出現錯誤訊息.告知超過 Maxvalue (5).

當我們使用 sequence 時,需要注意是否達到上限.
sequence 是使用 smallint, integer, bigint 三種,分別是 2 bytes, 4 bytes, 
8 bytes. default 只使用正數,不含 0.至於為何要這樣設計呢?
一來是符合一般的習慣,二來是可以另有妙用,這留待後續會分享.

先來討論這三種數值,是否足夠使用.
可以觀察昨天的資料知道 是 32 KB, 2048 MB, 8388608 TB.
或者可以查文件,然後複製 bigint(int8) 的一長串數字,來計算.

假設每分鐘輸入 10K 筆資料.一年約

select 10000 * 1440 * 365::bigint;
+------------+
|  ?column?  |
+------------+
| 5256000000 |
+------------+

其實看到 使用 bigint 來計算就知道 2048M 是不夠的.
改成計算週數

select 2147483647 / (10000 * 1440 * 7::bigint);

+----------+
| ?column? |
+----------+
|       21 |
+----------+

僅21週! 使用 integer 型態的 sequence ,就要很小心.

那接著來看使用 bigint (int8), 的情況下,若每分鐘輸入 1M 筆資料,可以撐多久?
因為我不想去copy那一串數字,所以改用計算的方式.

select big / (1000000 * 1440 * 365::bigint) as "year"
  from (select  -(((2^(8*pg_column_size(1::bigint)-2))::bigint << 1)+1) as big) a;

+----------+
|   year   |
+----------+
| 17548272 |
+----------+

還蠻久的,使用 bigint 型態的情況下,只要小心檢查,適當的移轉舊資料,應該是比較安全.

今天做了一些關於sequence 的容量相關介紹,昨天有寫一個 function,但是還不夠完備.
看了今天的介紹,理解了 Minvalue, Maxvalue 是可以自行調整,而且方向可以是遞增或是
遞減,情況就顯得比較複雜了,怎樣修改 function 讓他可以正確的計算,這個先留給大家思考一陣子.
我會在本系列結束之前,將我的方法與大家分享.
明天將探討 sequence 使用在 table 的情況.