タイムトラベル参照（モックアップ）
タイムトラベル参照って何ぞ？
以前、DB2の10.1だったかに実装された「タイムトラベル参照」なる機能がなかなか面白そうなだと思い、PostgreSQLでも似たような機能が拡張できないか、と考えていた。
(参考：テンポラル表を使用した時間に基づくデータの管理および照会)
もちろんDB2が備えているような機能を全部やろうとは思わないが、まず更新履歴情報を持つデータ型というものがPostgreSQLで可能なのか考えてみた。
基本方針
残念ながら、おそらく今のPostgreSQLの本体側に手を加えずに、型拡張だけでこれを実現するのは難しそうな気がする。というのは、型への入力関数内で、更新対象となる行・列の更新前データを取得出来なさそうだからだ。
このため、更新履歴を保持する型拡張と列トリガの組み合わせで妥協することにする。
モックアップ
とりあえず、テキスト型をカンマ区切りで連結するトリガ関数と、列トリガ定義、そして最新バージョン/前バージョンの値を取得する関数をplpgsqlで作ってみた。
こんな感じで動作する。
CREATE TABLE test (id int, section text, name text);
CREATE TABLE
CREATE TRIGGER update_text_concat
BEFORE UPDATE OF section ON test
FOR EACH ROW
EXECUTE PROCEDURE update_text_concat();
CREATE TRIGGER
INSERT INTO test VALUES (1,'P1BU', 'nuko yokohama'),(2,'P1BU', 'Tom Lane'),(3, 'TEC', 'Josh Berkus');
INSERT 0 3
SELECT id, current_version(section) as section, name FROM test ORDER BY id;
id | section | name
----+---------+---------------
1 | P1BU | nuko yokohama
2 | P1BU | Tom Lane
3 | TEC | Josh Berkus
(3 rows)テーブルを定義し、そのテーブルのsectoin列にUPDATE列トリガを設定する。
で、3行ほどINSERTして結果を表示する。
current_version()という関数は最新の版を取り出すユーザ定義関数だ。まだ各行のsection列はINSERT直後なので1世代しかバージョンを持っていない。
で、このテーブルに対して以下のようにUPDATEをかけてみる。
UPDATE test SET section = 'TDC' WHERE id = 2; UPDATE 1 UPDATE test SET section = 'SPC' WHERE id = 3; UPDATE 1 UPDATE test SET section = 'BDO' WHERE id = 2; UPDATE 1 SELECT id, current_version(section) as section, name FROM test ORDER BY id; id | section | name ----+---------+--------------- 1 | P1BU | nuko yokohama 2 | BDO | Tom Lane 3 | SPC | Josh Berkus (3 rows)
- id=1の行のsectionは更新されていない。
- id=2の行のsectionは2回更新され3つの世代を持っている(P1BU→TDC→BDO)。
- id=3の行のsectionは1回更新され2つの世代を持っている(TEC→SPC)。
この状態のテーブルに対して、SELECTを発行する。ただし、section列に対してユーザ定義関数 previous_vesion()をかけ、過去の世代の値を取り出してみる。
SELECT id, previous_version(section, 1) as section, name FROM test ORDER BY id; id | section | name ----+---------+--------------- 1 | P1BU | nuko yokohama 2 | TDC | Tom Lane 3 | TEC | Josh Berkus (3 rows) SELECT id, previous_version(section, 2) as section, name FROM test ORDER BY id; id | section | name ----+---------+--------------- 1 | P1BU | nuko yokohama 2 | P1BU | Tom Lane 3 | TEC | Josh Berkus (3 rows)
最初のクエリでは1世代前の値を取得する指定を行っている。なお、id=1の行については1つしか世代を持っていない(最新世代しかない)ので、それをそのまま返している。
次のクエリでは2世代前の値を取得する指定を行っている。なお、id=3の行については2つしか世代を持っていないので、最古(INSERT直後)の値を返している。
種明かし
今回のモックアップでは単にトリガ関数でカンマ区切りでテキストを連結し、各関数でカンマ区切りの文字列からの抽出を行っているだけ。
普通に関数をかけずにsection列を表示するとこんな感じ。
SELECT id, section, name FROM test ORDER BY id; id | section | name ----+--------------+--------------- 1 | P1BU | nuko yokohama 2 | BDO,TDC,P1BU | Tom Lane 3 | SPC,TEC | Josh Berkus (3 rows)
もちろん、真面目に対応していないのでデータ内にカンマを含むとバグるｗ
なお、トリガ関数や世代取得関数は今回はこんな感じで実装した。
-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION update_text_concat() RETURNS trigger AS $$
DECLARE
concated TEXT;
BEGIN
concated := NEW.section || ',' || OLD.section ;
NEW.section = concated ;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Get Current Versoin
CREATE OR REPLACE FUNCTION current_version(input_text text) RETURNS text AS $$
DECLARE
pos integer;
BEGIN
pos := (SELECT position(',' in input_text));
IF pos = 0 THEN
RETURN input_text;
ELSE
RETURN substring(input_text, 1, pos - 1);
END IF;
END;
$$ LANGUAGE plpgsql;
-- Get Previous Versoin
CREATE OR REPLACE FUNCTION previous_version(input_text text, version integer) RETURNS text AS $$
DECLARE
splited_array text[];
splited_num integer;
idx integer;
BEGIN
splited_array := (SELECT regexp_split_to_array(input_text, ','));
splited_num := (SELECT array_length(splited_array, 1));
IF version + 1 > splited_num THEN
idx = splited_num;
ELSE
idx = version + 1;
END IF;
RETURN splited_array[idx];
END;
$$ LANGUAGE plpgsql;
今後
で、これをきちんとC言語関数を使って実装してみようと思う(トリガ関数は面倒だからplpgsqlを使うかもしれない)。
合わせて以下の様な機能追加もやってみよう。
- 今回のモックアップで使った、current_version()相当を、型の出力関数として実装することで、普通に列名だけをSELECT句に指定した場合、最新バージョンを取得できるようにする。
- 今回はバージョン番号のみの制御だったが、実用上を考えるとタイムスタンプ指定により、直近(過去 or 未来)のバージョンの取得を可能とする。
- 監査情報として全バージョン情報の表示を行う。形式は要検討。JSONが楽かな？
- そして未来の日付と値を組でセットすることで、時限発火的にSELECTしたときの値を変更させるなどの機能
果たして実用上役に立つのかどうかは不明だが・・・
