シーケンスの作成と削除
シーケンスの作成SQL
シーケンス名recnoを作成しています。PostgreSQLのシーケンスキャッシュのデフォルト値は1です。Oracleでの経験ですが、キャッシュ値を1にするとかなり遅くなる事経験したのでここではあえて20に設定しています。PostgreSQLではそれほど関係ないのですかね？今度機を見て検証してみようとおもいます。（参考までにですが、Oracleでのシーケンスキャッシュ値のデフォルトは20です。）
create sequence sooni.recno increment 1 no maxvalue start 1 cache 20 cycle;
myposdb=# create sequence sooni.recno increment 1 no maxvalue
myposdb-# start 1 cache 20 cycle;
CREATE SEQUENCE
myposdb=#シーケンスを削除するSQL
drop sequence sooni.recno ;
シーケンスの確認
以下３パターンの確認方法を用意しました。
- シーケンス定義の確認SQL1（pg_sequenceのみ参照のケース）
- シーケンス定義の確認SQL2（pg_sequenceとpg_classを結合し参照するケース） （おすすめ）
- シーケンス定義の確認SQL3（information_schema.sequencesを参照）
１．シーケンス定義の確認SQL（pg_sequenceのみ参照のケース）
最も簡単な方法で、pg_sequenceの参照だけで確認でき便利ですが、スキーマを確認できません。（サーチパスに従って最初に見つかったシーケンスのみ確認できます。今回のように直前で作成したシーケンスを確認するのであれば必要十分ではあります。）またOracleではdba_sequencesに相当しそうですが、LAST_NUMBER（補足１）を保持していません。
(補足1) そのシーケンスは現在何番まで使われているか。（キャッシュされている値もあるので厳密には少し違うのですが、少なくともこの番号までは利用されている事がわかります）
select seqrelid::regclass ,seqtypid::regtype ,seqstart,seqincrement,seqmax ,seqmin,seqcache,seqcycle from pg_sequence where seqrelid = 'recno'::regclass;
確認例
-[ RECORD 1 ]+--------------------
seqrelid | recno
seqtypid | bigint
seqstart | 1
seqincrement | 1
seqmax | 9223372036854775807
seqmin | 1
seqcache | 20
seqcycle | t
myposdb=#２．シーケンス定義の確認SQL（pg_sequenceとpg_classを結合し参照するケース）
pg_classと結合する事で、アクセスできるシーケンスは全て確認できます。シーケンス一覧などはこちらの方が良いですね。以下はシーケンス名（recno）で絞った一覧です。
select c.relowner ::regrole as owner ,c.relnamespace::regnamespace as schema ,c.relname ,s.seqtypid :: regtype ,s.seqstart,s.seqincrement,s.seqmax,s.seqmin,s.seqcache,s.seqcycle ,c.reltype::regtype ,array_to_string(relacl,',') relacl from pg_sequence s inner join pg_class c on s.seqrelid = c.oid where c.relname ='recno';
確認例
myposdb=# select
myposdb-# c.relowner ::regrole as owner
myposdb-# ,c.relnamespace::regnamespace as schema
myposdb-# ,c.relname
myposdb-# ,s.seqtypid :: regtype
myposdb-# ,s.seqstart,s.seqincrement,s.seqmax,s.seqmin,s.seqcache,s.seqcycle
myposdb-# ,c.reltype::regtype
myposdb-# ,array_to_string(relacl,',') relacl
myposdb-# from pg_sequence s
myposdb-# inner join pg_class c on s.seqrelid = c.oid
myposdb-# where c.relname ='recno';
-[ RECORD 1 ]+---------------------------------
owner | sooni
schema | sooni
relname | recno
seqtypid | bigint
seqstart | 1
seqincrement | 1
seqmax | 9223372036854775807
seqmin | 1
seqcache | 20
seqcycle | t
reltype | recno
relacl | sooni=rwU/sooni,udonman=rU/sooni
-[ RECORD 2 ]+---------------------------------
owner | udonman <-- 別オーナ
schema | public <-- 別スキーマ
relname | recno <-- 同一シーケンス名
seqtypid | bigint
seqstart | 300
seqincrement | 10
seqmax | 9223372036854775807
seqmin | 1
seqcache | 50
seqcycle | t
reltype | public.recno
relacl |
myposdb=#３．シーケンス定義の確認SQL（information_schema.sequencesを参照）
information_schema.sequencesの参照でもアクセスできるシーケンス全て確認できますが、キャッシュ値が見当たりません。
select * from information_schema.sequences where sequence_name ='recno' ;
確認例
myposdb=# \x
Expanded display is on.
myposdb=# select * from information_schema.sequences
myposdb-# where sequence_name ='recno'
myposdb-# ;
-[ RECORD 1 ]-----------+--------------------
sequence_catalog | myposdb -- 属するデータベース
sequence_schema | public -- 属するスキーマ
sequence_name | recno -- シーケンス名
data_type | bigint
numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0
start_value | 300 -- シーケンス開始値
minimum_value | 1 -- シーケンス最小値
maximum_value | 9223372036854775807 -- シーケンス最大値
increment | 10 -- 増加値（キャッシュがあるのでこの値が保証されるものではありません）
cycle_option | YES -- 最大値を超えて周回するのかどうか
-[ RECORD 2 ]-----------+--------------------
sequence_catalog | myposdb
sequence_schema | sooni
sequence_name | recno
data_type | bigint
numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0
start_value | 1
minimum_value | 1
maximum_value | 9223372036854775807
increment | 1
cycle_option | YES
myposdb=#シーケンス名をそのままselectすると現状を確認できます
select * from sooni.recno ;
myposdb=# create sequence sooni.recno increment 1 no maxvalue
myposdb-# start 1 cache 20 cycle;
CREATE SEQUENCE
myposdb=# select * from sooni.recno;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f <-- まだ何処からもnextvalされていない
(1 row)
myposdb=# select nextval('sooni.recno');
nextval
---------
1
(1 row)
myposdb=# select * from sooni.recno;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 32 | t
(1 row)
myposdb=#シーケンス操作関数
| 操作関数 | 説明 | 補足 |
| nextval | パラメータで指定するシーケンスを進める | |
| currval | 現在のセッションにて、パラメータで指定するシーケンスで直近nextvalで取得した値を返す。 | 該当セッションにて一度もnextvalしていない場合、当関数は利用できない。 |
| lastval | 現在のセッションにて、直近nextvalで取得した値を返す。 | 当関数ではファンクション名の指定はできない。同一セッション内で複数シーケンスのnextvalを行った場合、直近のシーケンスが対象になる。 |
| setval | パラメータで指定するシーケンスの値を指定の値にする。 | Oracleではalter sequenceを利用するしかありませんがこれは便利ですね。Oracleでalter sequenceするという事はトランザクションがそこで切れてしまう事になるのですが、setvalを使ってもトランザクションが切れる事はありません。 |
シーケンス操作関数の利用例
以下に２つのセッションA、Bを使って動作を確認します。シーケンス値が必ずしも通番にならない事（飛び番が発生しうる事）、必ずしもシーケンス値の大小が時系列を保証するものではない事わかります。
-- セッションAにて以下オペレーション
--
-- シーケンスの作成
myposdb=# create sequence sooni.recno increment 1 no maxvalue
myposdb-# start 1 cache 20 cycle;
CREATE SEQUENCE
-- 現在のlast_valueを確認
myposdb=# select last_value from sooni.recno;
last_value
------------
1
(1 row)
-- 1回目のnextval
myposdb=# select nextval('sooni.recno');
nextval
---------
1
(1 row)
-- 現在のlast_valueを確認
myposdb=# select last_value from sooni.recno;
last_value
------------
20 <-- キャッシュ値が加味されている
(1 row)
-- 2回目のnextval
myposdb=# select nextval('sooni.recno');
nextval
---------
2 <-- last_valueは20であっても想定通り2が取得できる。
(1 row)
-- currval()にて現在値の確認
myposdb=# select currval('sooni.recno');
currval
---------
2
(1 row)
-- lastval()にて現在値の確認
myposdb=# select lastval();
lastval
---------
2
(1 row)
myposdb=#
-- ここでセッションBにて以下オペレーション
--
myposdb=# select nextval('sooni.recno');
nextval
---------
21 <-- セッションAのキャッシュ20により、取得値は21となる
(1 row)
-- 現在のlast_valueを確認
myposdb=# select last_value from sooni.recno;
last_value
------------
40 <-- セッションBのキャッシュ20が加味されている
(1 row)
myposdb=#-- ここでまたセッションAに戻りオペレーション
--
myposdb=# select nextval('sooni.recno');
nextval
---------
3 <-- キャッシュしてある値(20個分）を使いきるまでは１つづつインクリメント
(1 row)
myposdb=#シーケンス値の更新(setval)の利用例
-- 以下はシーケンスrecnoのシーケンス値を70に更新しています
--
select setval('sooni.recno',70);Oracleと比較し特徴的なのはPostgreSQLのsetval()によるシーケンス値更新はトランザクションが途切れない事です。Oracleの場合alter sequenceを発行したタイミングでcommitが入ります。以下にsetval()を行ってもトランザクションが途切れない事確認しました。
# 最初にfruitテーブルの値を確認する
#
myposdb=# select * from fruit order by 1;
fid | name | price | discount
-----+--------+-------+----------
101 | バナナ | 200 | 10
102 | リンゴ | 100 | 15
103 | メロン | 300 | 70
104 | ブドウ | 260 | 40
(4 rows)
# トランザクション開始
#
myposdb=# start transaction;
START TRANSACTION
# fruitテーブルに対するupdate実施
#
myposdb=*# update fruit set name='林檎' where fid = 102;
UPDATE 1
# シーケンス値を70に更新
#
myposdb=*# select setval('sooni.recno',70);
setval
--------
70
(1 row)
# ロールバックの実施
#
myposdb=*# rollback;
ROLLBACK
# fruitテーブルの値確認（update前の状態に戻っている）
#
myposdb=# select * from fruit order by 1;
fid | name | price | discount
-----+--------+-------+----------
101 | バナナ | 200 | 10
102 | リンゴ | 100 | 15
103 | メロン | 300 | 70
104 | ブドウ | 260 | 40
(4 rows)
# シーケンス値は更新後の値である事確認
#
myposdb=# select last_value from sooni.recno;
last_value
------------
70
(1 row)
myposdb=#
