« C/Sシステムのウェブ化でサーバが減る? そんなわけないじゃん | トップページ | 大人になったなと思うこと »

2014.02.13

シーケンス番号を自在に操りたい!(1)-連番周回

PostgreSQLにおけるシーケンス型(Serial型)は多少のクセがあり、今まで使ってなかったのだけども、ちょっち需要があったので調べものをしてみた。
要望としてはシーケンス型を一定の範囲内で使用し、桁数の最大値に達したら1に戻って利用したい、というサイクリック型でのデータ運用がしたい、ということであった。
結論から言うと、ちょいとパラメータをいじれば簡単に実現できる。
ただ、その事実に気づく前にちょこっと自前でサイクリックを実現させてみたので、悔しいのでそれも記載しておく。

自前で頑張ったパターンでの実装(非推奨)
実はPostgreSQLの機能で実現できる方法(推奨)

いろいろ試してみる前に、PostgeSQLの連番型の仕様について確認をしておこう。

●作成方法
-カラム作成時にSERIAL(BIGSERIAL)型で型を指定して作成する

CREATE TABLE test_seq(seq serial NOT NULL, var integer NOT NULL DEFAULT 0,
CONSTRAINT test_seq_pkey PRIMARY KEY (seq)
);
こうすると、seq列は連番型としてデータ生成されるとともに、デフォルトでシーケンスジェネレータが生成される。
PostgreSQLにおける連番情報は、各カラムにセットされるのではなくシーケンスデータに蓄積される。
今回の場合はDefault挙動に任せたので"test_seq_seq_seq"というシーケンスが作成されている。適当に命名してしまったので、大変わかりにくい。_seqが自動的につけられてしまうので、カラム名にはseqなどと使わないほうがわかりやすいかも。
まあ、このジェネレータを直接操作することはほとんどないので、問題ないといえば問題ないと思うが。

pgAdminIIIなどを使用すれば、このシーケンス情報を簡単に見ることができる

●注意点
PostgereSQLのver8以後、連番型は一意性制約を自分ではもたないようになっている。このため、通常はユニーク性を担保するためにPrimary Keyにしたり、ユニーク制約をかけたりすることが重要となる。
今回は主キーとして設定したため、一意性はこれにて担保される。


◆サイクリックの実装


気合を入れて、自力でサイクリックするロジックを作りこんでみる。
理屈の上では次のとおり
-INSERTトリガーを設定
-新たにINSERTされる連番値が最大値になった場合、連番の次の値を初期化する
そこで、今回は6桁の数値をとるものと仮定して999999を最大値として、その後1に戻る、という実装をしたい。
シーケンスの初期値は最低値1となっている。ただし、循環して帰ってきたときに1を返したいため、裏側のデータとしては0を所持できるようにしておく必要があるので
ALTER SEQUENCE test_seq_seq_seq MINVALUE 0;
こうやって最小値に0を指定しておく。

ではいよいよトリガーとトリガー関数を作成する。

CREATE OR REPLACE FUNCTION init_test_seq()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF NEW.seq = 999999 THEN
    PERFORM setval('test_seq_seq_seq', 0);
  ELSE
  END IF;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;

CREATE TRIGGER ti_test_seq
  BEFORE INSERT
  ON test_seq
  FOR EACH ROW
  EXECUTE PROCEDURE init_test_seq()
;


処理としては今回インサートされた値が最大値に達していた場合、次の値を初期化する、ということにある。
この挙動を確認するには下記のようなデータを使ってみるといいだろう。
SELECT setval('test_seq_seq_seq',999998);
INSERT INTO test_seq(var) VALUES(999999);
INSERT INTO test_seq(var) VALUES(1000000);
INSERT INTO test_seq(var) VALUES(1000001);
SELECT * FROM test_seq;
1行目のSeq値は99999であるが、2行目は1に戻っていることがわかるだろう。

これにて自作サイクリック処理の完成である。


◆そんなことより簡単に設定しようぜ


実はこのシーケンスデータについてはいろいろとALTER SECUENCEで設定できるオプションがある。
今回一生懸命周回を作ったもののそれも設定一発でできてしまうのである。
ALTER SEQUENCE test_seq_seq_seq
CYCLE MAXVALUE 999999;
これだけでよい。

CYCLEパラメータはデフォルトでは省略されており、NO CYCLE(循環しない)のがデフォルトとなっている。
また周回設定をしたい場合は最大値も設定したいはずだが、それもMAXVALUEパラメータで設定が可能である。
このほかにも100000-999999でサイクリックしたいのであればMINVALUEというパラメータもあるので

ALTER SEQUENCE test_seq_seq_seq
CYCLE START 100000 MAXVALUE 999999 MINVALUE 100000;
とすればOKである。
ただし、デフォルトで作成されたシーケンスの場合、初期値(START)は1となっているため、上記のALTER SQL文は失敗する。
この場合は、MINVALUEを設定する前に現在値をMINVALUE以上にしておく必要がある。
すなわち
SELECT setval('test_seq_seq_seq',100000);
などとしてから、先ほどのSQLを実行する必要がある、ということである。

そして完成したのが下記のとおり。

「循環?:はい」とか「最大値、最小値、現在値」など、自在に変更されていることがわかるだろう。
……さらにいうと、こうした設定自体はpgAdminIIIを使えばGUIから簡単に変更することができる(^^;

[限界リセット]というチェックボックスがCYCLE/NO CYCLEの切替を行うパラメータである。
pgAdminIII上の翻訳がピンとこないのでちょっとわかりにくいところではあるが。

とはいえなんというか、車輪の再発明をした気分満載である(^^;;

|

« C/Sシステムのウェブ化でサーバが減る? そんなわけないじゃん | トップページ | 大人になったなと思うこと »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59123536

この記事へのトラックバック一覧です: シーケンス番号を自在に操りたい!(1)-連番周回:

« C/Sシステムのウェブ化でサーバが減る? そんなわけないじゃん | トップページ | 大人になったなと思うこと »