Postgresql 的 Sequence 關聯物件

Postgresql 與 Oracle 均有 Sequence 此一關聯物件.
為何叫做關聯物件? 關聯物件包含 Table / View / Sequence 等,
另外有 foreign table (PG) 或是 External Table (Oracle) 等延伸型態.
關聯物件都有相同的基本屬性,在前面的 四探 Postgresql Catalog 中

https://ithelp.ithome.com.tw/articles/10218574

已經有出現關於 Sequence 的查詢 pg_class, pg_sequence 的方法.

Sequence 甚至可以直接查詢相關資訊欄位, 如 極大值,遞增值等.
在 10 版以後,一些相關資訊移到pg_sequence 存放,留下三個欄位,一般只需查 last_value, is_called.

通常在 psql 下 透過 \d 就能列出 關聯物件, sequence 也是關聯物件會一起顯示.
\d seq_name 這樣就能顯示一些相關資訊,如下所示:

\d t190920_testid_seq
                   Sequence "miku.t190920_testid_seq"
+---------+-------+---------+------------+-----------+---------+-------+
|  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache |
+---------+-------+---------+------------+-----------+---------+-------+
| integer |     1 |       1 | 2147483647 |         1 | no      |     1 |
+---------+-------+---------+------------+-----------+---------+-------+
Owned by: miku.t190920.testid

其實就是透過 pg_seqnence 等查詢,而且注意到是有跟 table miku.t190920 的欄位 testid 
有關聯.

這是之前 四探 Postgresql Catalog 中使用的.但是注意到這裡的資訊是沒有 sequence 使用到哪裡了.
這時候要直接查詢 sequence 的 last_value 欄位,而不是用 select currval('sequence');
會得到錯誤
select currval('sequence');
ERROR:  55000: currval of sequence "t190920_testid_seq" is not yet defined in this session

若是呼叫取值 nextval(), 該值就跳過了.

select last_value
  from t190920_testid_seq;

+------------+
| last_value |
+------------+
|          1 |
+------------+

若是要逐一檢查,對DBA的日常作業來說,負擔頗重,故一般會撰寫一些 function 來輔助.
後面會跟大家分享一個 function.在這之前,先來建立兩個 sequence.並進行操作

create sequence nana.s1 
as smallint;

create sequence nana.s2
as bigint;

select nextval('nana.s1');

+---------+
| nextval |
+---------+
|       1 |
+---------+

select setval('nana.s1', 3);

+--------+
| setval |
+--------+
|      3 |
+--------+

select nextval('nana.s1');

+---------+
| nextval |
+---------+
|       4 |
+---------+

select nextval('nana.s2');

+---------+
| nextval |
+---------+
|       1 |
+---------+

select setval('nana.s2', 40000, false);

+--------+
| setval |
+--------+
|  40000 |
+--------+

select nextval('nana.s2');

+---------+
| nextval |
+---------+
|   40000 |
+---------+

兩次設定值的操作,差異在 is_called flag 的設定.default 是 true.
設定為 false時, nextval()會取目前值,反之則取下一值.
當呼叫 nextval()取值時,亦會設定 is_called flag.

接著就是剛才提到的 function.

-- ****** function start ******
create or replace function sequence_list()
returns table( seq_name text
        , seqtyp text
        , is_call boolean
        , current_value bigint
        , limt bigint
        , remaining bigint)
as $code$
declare
    cmd text;
    schemaz name;
    seqz name;
    seqid oid;
begin
    for schemaz, seqz, seqid in
        select relnamespace::regnamespace
             , relname
             , oid
          from pg_class
         where relkind = 'S'
         order by relnamespace
    loop
        raise debug 'Inspecting %.%', schemaz, seqz;
        
        cmd := format('select ''%s.%s''
                            , s.seqtypid::regtype::text
                            , is_called
                            , last_value
                            , s.seqmax
                            , (s.seqmax - last_value) / s.seqincrement
                         from %I.%I
                            , pg_sequence s 
                        WHERE s.seqrelid = %s'
                      , quote_ident( schemaz )
                      , quote_ident( seqz )
                      , schemaz
                      , seqz
                      , seqid
               );
        raise debug 'Query [%]', cmd;
        return query execute cmd;
    end loop;
end
$code$
language plpgsql
strict;

-- ****** function end ******
'
可以用以下方式呼叫
select * from sequence_list();

或是再搭配函數 pg_size_pretty() 提高數字的可讀性,
當然也可以利用 where 做過濾,或是排序,這些部分可自行
靈活運用.

select seq_name
     , seqtyp
     , is_call
     , current_value
     , pg_size_pretty(limt)
     , pg_size_pretty(remaining)
  from sequence_list();

+-[ RECORD 1 ]---+---------------------------+
| seq_name       | miku.sample_arr_id_seq    |
| seqtyp         | integer                   |
| is_call        | t                         |
| current_value  | 2                         |
| pg_size_pretty | 2048 MB                   |
| pg_size_pretty | 2048 MB                   |
....
+-[ RECORD 10 ]--+---------------------------+
| seq_name       | nana.s1                   |
| seqtyp         | smallint                  |
| is_call        | t                         |
| current_value  | 4                         |
| pg_size_pretty | 32 kB                     |
| pg_size_pretty | 32 kB                     |
+-[ RECORD 11 ]--+---------------------------+
| seq_name       | nana.s2                   |
| seqtyp         | bigint                    |
| is_call        | t                         |
| current_value  | 40000                     |
| pg_size_pretty | 8388608 TB                |
| pg_size_pretty | 8388608 TB                |
+----------------+---------------------------+

這樣就能方便觀察 sequence 情況, 主要是針對數值部分.
至於 cache / cycle 這兩個值,透過 psql \d 時就會顯示,
在上面就有例子了.為了讓函數精簡起見,就不列入其中.

但這個函數是有缺陷的.原因在 計算 remaining 時的算式
(s.seqmax - last_value) / s.seqincrement 

雖然有考慮到了 seqincrement , 但是 Postgresql 的 sequence 
不全然是遞增的,seqincrement 可以設定為 負值,此時是遞減的.
計算時應該改用 seqmin,不過沒關係.明天我們將再繼續就 sequence 
及其應用做探討,屆時再來修正此函數.