ファイヤープロジェクト
シーケンスとシリアル型
2003-12-30T17:00+09:00   matsu
シーケンスという連番自動生成機能とシーケンスによるシリアル型について.
シーケンスを作成するには,CREATE SEQUENCEを実行する(※).
firstdb=> \h CREATE SEQUENCE
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]
    [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
    [ START start ] [ CACHE cache ] [ CYCLE ]

各オプションについて以下に示す.
TEMPORARY,TEMP
セッション内でのみ有効な一時シーケンスを作成.
seqname
シーケンス名.DB内で一意でなければならない.
INCREMENT increment
増加幅をincrementとする.デフォルトは1.負の値を指定すると減少するシーケンスを作成できる.
MINVALUE minvalue
シーケンスの最小値をminvalueとする.デフォルトは増加シーケンスでは1,減少シーケンスでは-(2**31).
MAXVALUE maxvalue
シーケンスの最大値をmaxvalueとする.デフォルトは増加シーケンスでは2**31,減少シーケンスでは-1.
START start
シーケンスの初期値をstartとする.デフォルトは増加シーケンスではminvalue,減少シーケンスではmaxvalue.
CACHE cache
高速化のために各バックエンドプロセスがcache個のシーケンスを一度に確保する.確保する値はバックエンド間で重複することはない.複数のバックエンドの実行順は保証されないので,使用されるシーケンス値の順番が前後することがある.キャッシュした値が最終的に使用されなかった場合は,その値は失われる.例えばバックエンドB1,B2がそれぞれシーケンス{1,2,3},{4,5,6}をキャッシュしているとする.ここで,B1,B2,B2,B1,B1の順番でバックエンドが動いた場合,消費されるシーケンス値は順に1,4,5,2,3であり,順番が前後する.ここでB2にはシーケンス値6が消費されずに残っているが,このままB2が終了すると,値6は使用されずに消失する.
CYCLE
サイクリックなシーケンスにする時に使用する.サイクリックな増加シーケンスではmaxvalueに達したらminvalueにもどる.サイクリックな減少シーケンスではその逆.
シーケンスを削除するにはDROP SEQUENCEを実行する.
DROP SEQUENCE seqname;
※ シーケンスはSQL標準ではない.
作成したシーケンスは,それ用の関数によって操作する.
nextval('シーケンス名')
次のシーケンス値を返す.
currval('シーケンス名')
現在のシーケンス値を返す.同じセッション内でnextval実行後でないとエラーになる.現在のシーケンス値はcurrvalのかわりに以下でも取り出せる.
SELECT last_value FROM シーケンス名;
setval('シーケンス名', 値)
シーケンスの値を設定する.
では,実際にやってみる.
firstdb=> CREATE SEQUENCE test_seq;
CREATE

firstdb=> \d test_seq
   Sequence "test_seq"
    Column     |  Type   
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean
作成したシーケンスtest_seqを使用するテーブルhogeを作成.
firstdb=> CREATE TABLE hoge (
firstdb(> seq INTEGER DEFAULT nextval('test_seq'),
firstdb(> val INTEGER);
CREATE
hogeにINSERTしてみる.
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26156 1
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26157 1
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26158 1
firstdb=> SELECT * FROM hoge;
 seq | val 
-----+-----
   1 |   1
   2 |   1
   3 |   1
(3 rows)
シーケンスtest_seqを消してみる.
firstdb=> DROP SEQUENCE test_seq ;
DROP
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
ERROR:  pg_aclcheck: class "test_seq" not found
確かにシーケンスが消えているようだ.なお,シーケンスはROLLBACKしても元には戻らない.
シリアル型は1から始まる増加シーケンスを使用したデータ型である.シリアル型を使用すると,自動的にシーケンスが作成され,その属性でインデクスが作成される.テーブルをDROPしてもシーケンスはDROPされないので,以下のパターンで作成されたシーケンスを別途DROPする必要がある.
テーブル名_属性名_seq
では,やってみる.
firstdb=> CREATE TABLE hoge (
firstdb(> id SERIAL,
firstdb(> val INTEGER);
NOTICE:  CREATE TABLE will create implicit sequence 'hoge_id_seq' for SERIAL column 'hoge.id'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'hoge_id_key' for table 'hoge'
CREATE
あとは,シーケンスの場合と同様に扱える.
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26164 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26165 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26166 1
firstdb=> SELECT * FROM hoge ;
 id | val 
----+-----
  1 |   1
  2 |   1
  3 |   1
(3 rows)
SERIALはUNIQでなければならない.
firstdb=> INSERT INTO hoge (id,val)
firstdb-> VALUES (1,2);
ERROR:  Cannot insert a duplicate key into unique index hoge_id_key
さらにSERIAL型の列に自分で勝手に値を放り込んでも,シーケンスにはそれがわからないので,できるだけ避けたい.
firstdb=> INSERT INTO hoge (id,val)
firstdb-> VALUES (5,2);
INSERT 26168 1
firstdb=> SELECT * FROM hoge ;
 id | val 
----+-----
  1 |   1
  2 |   1
  3 |   1
  5 |   2
(4 rows)

firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26169 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
ERROR:  Cannot insert a duplicate key into unique index hoge_id_key
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26171 1
firstdb=> SELECT * FROM hoge ;
 id | val 
----+-----
  1 |   1
  2 |   1
  3 |   1
  5 |   2
  4 |   1
  6 |   1
(6 rows)
最後に消しておく.
firstdb=> DROP TABLE hoge ;
DROP
firstdb=> DROP SEQUENCE hoge_id_seq;
DROP
matsu(C)
Since 2002
Mail to matsu