履歴情報つきinteger試作
三連休、みなさんいかがお過ごしでしょうか。
私は秦野の万葉倶楽部でお風呂三昧の傍ら、役にたたないPostgreSQL拡張を作ってますｗ
今回作ったのは、履歴情報つきinteger。この構想自体は数年前からあったけど、久々に作りなおしてみることにした。
実装方針
最初はまっとうにCREATE TYPEしてC言語で作ろうかと思ったわけなんだけど、管理情報を設計しているうちに、
これ、フツーにJSONBで管理できる範疇じゃね？
と思い、方針を完全に変更し、JSONB型の応用例として作ってみることにした。
なので、実装方法もC言語でごりごり書くのではなく、SQL関数とplpgsql関数のみで済ますことに。型自体はJSONBのまま使い、SQL関数をラップすることで履歴情報integerを擬似することにした。
作ったのはこんな関数群。value(jsonb, timestamptz)のみplpgsqlで実装し、あとはSQL関数で済ませるという手抜きっぷり。
| 関数名 | 内容 | 返却型 | 引数 |
| add_tt_int | integer更新の履歴を追加 | jsonb | src jsonb, value integer |
| create_leaf_value | (内部関数) | jsonb | value integer |
| create_tt_int | (更新情報付きinteger生成) | jsonb | value integer |
| header | ヘッダ情報取得 | jsonb | src jsonb |
| newest | 最新タイムスタンプ取得 | timestamp with time zone | src jsonb |
| nums | 世代数取得 | integer | src jsonb |
| oldest | 最古タイムスタンプ取得 | timestamp with time zone | src jsonb |
| value | 最新の値を取得 | integer | src jsonb |
| value | 世代番号を指定して値を取得 | integer | src jsonb, in_t timestamp with time zone |
| value | タイムスタンプを指定して値を取得 | integer | src jsonb, num integer |
使用例
こんな感じで使う。
cretae_tt_int()による履歴情報つきintegerの生成。
まず、挿入時に cretae_tt_int() に整数値を与えて、履歴情報つきinteger(実体はJSONB)を生成する。
test=# CREATE TABLE test (id int, data jsonb); CREATE TABLE test=# INSERT INTO test VALUES (1, create_tt_int(100)); INSERT 0 1 test=#
「tt ってなんの略よ？」という疑問もあると思うけど、Time-Travelの意味でttというのをつけた。深い意味はあまりない。
それはさておき、これで履歴情報つきintegerが生成された。
値をとりだすときは、value()を使う。
test=# INSERT INTO test VALUES (1, create_tt_int(100)); INSERT 0 1 test=# SELECT value(data) FROM test; value ------- 100 (1 row)
実際、テーブル内では、どう格納されているかというと、こんなJSONBデータとして格納されている。
そこ、オーバヘッド大きすぎとか言わない。
test=# SELECT jsonb_pretty(data) FROM test;
{ +
"header": { +
"nums": 1, +
"newest": "2016-03-21 09:52:31.453021+09", +
"oldest": "2016-03-21 09:52:31.453021+09" +
}, +
"values": [ +
{ +
"num": 1, +
"value": 100, +
"timestamp": "2016-03-21 09:52:31.453021+09"+
} +
] +
}
add_tt_int()による更新
次に、add_tt_int()を使って、履歴付きで値を100から200に更新する。
test=# UPDATE test SET data = add_tt_int(data, 200) WHERE id = 1; UPDATE 1 test=#
value(jsonb)で最新値を取り出す。
test=# SELECT value(data) FROM test WHERE id = 1; 200 test=#
更新した200が取得できる。
この状態で、1世代前の値(100)を取得してみる。
test=# SELECT value(data,1) FROM test WHERE id = 1; 100 test=#
はい。取得できましたね。
value(jsonb, timestamp with timezone)を使い時刻指定で値を取得
もうちょっと履歴を増やしてみる。
test=# SELECT now(); 2016-03-21 10:06:15.211285+09 test=# UPDATE test SET data = add_tt_int(data, 300) WHERE id = 1; UPDATE 1 test=# SELECT now(); 2016-03-21 10:06:26.639239+09 test=# UPDATE test SET data = add_tt_int(data, 400) WHERE id = 1; UPDATE 1 test=# test=# SELECT now(); 2016-03-21 10:06:34.204402+09 test=# UPDATE test SET data = add_tt_int(data, 500) WHERE id = 1; UPDATE 1 test=#
現在は5つの世代の履歴が格納されている。
最終更新のタイムスタンプは、10時6分38秒くらい。
test=# SELECT nums(data) FROM test WHERE id = 1;
5
test=# SELECT newest(data) FROM test WHERE id = 1;
2016-03-21 10:06:38.830039+09この状態で、10時6分25秒時点のdataの値を参照してみる。この時点では300になっているはず。
test=# SELECT value(data,'2016-03-21 10:06:25'::timestamptz) FROM test WHERE id = 1; 300 test=#
はい。取得できましたね。
まあ、こんな感じで使う。
役に立つのかどうかは知らん。
続編のベンチマーク測定で判明するけど、非常に更新世代数が増えると、この方式はとんでもなく遅くなってしまうので、使うとしたらせいぜい数十世代の管理が限界かなぁ、という気はしているが。
なんせ、5世代分の情報格納だけで、こんなJSONBデータになるからなあ・・・
test=# SELECT jsonb_pretty(data) FROM test WHERE id = 1;
jsonb_pretty
----------------------------------------------------------
{ +
"header": { +
"nums": 5, +
"newest": "2016-03-21 10:06:38.830039+09", +
"oldest": "2016-03-21 09:52:31.453021+09" +
}, +
"values": [ +
{ +
"num": 5, +
"value": 500, +
"timestamp": "2016-03-21 10:06:38.830039+09"+
}, +
{ +
"num": 4, +
"value": 400, +
"timestamp": "2016-03-21 10:06:31.403903+09"+
}, +
{ +
"num": 3, +
"value": 300, +
"timestamp": "2016-03-21 10:06:23.426476+09"+
}, +
{ +
"num": 2, +
"value": 200, +
"timestamp": "2016-03-21 10:01:11.80119+09" +
}, +
{ +
"num": 1, +
"value": 100, +
"timestamp": "2016-03-21 09:52:31.453021+09"+
} +
] +
}
実装コード
SQL関数/plpgsql関数として実装しているので、ちょい長いけど、ここに書いちゃう。
このコードはPostgreSQl 9.5以降のバージョンで動作します。
(jsonb_build_object() を json_build_object()::jsonb に書き換えれば、9.4でも動くかもしれないが未確認)
--
-- tt_type
--
--
-- integer value to jsonb value.
-- (internal)
--
CREATE OR REPLACE FUNCTION create_leaf_value(value integer) RETURNS jsonb AS $$
SELECT jsonb_build_object('value', value);
$$ LANGUAGE sql;
--
-- integer value to tt_int(jsonb) value.
--
CREATE OR REPLACE FUNCTION create_tt_int(value integer) RETURNS jsonb AS $$
SELECT
jsonb_build_object(
'header', jsonb_build_object(
'nums', 1,
'newest', now()::text,
'oldest', now()::text),
'values', jsonb_build_array(
jsonb_build_object(
'value', value,
'timestamp', now()::text,
'num', 1
)
)
);
$$ LANGUAGE sql;
--
-- add integer to tt_int(jsonb)
--
CREATE OR REPLACE FUNCTION add_tt_int(src jsonb, value integer) RETURNS jsonb AS $$
SELECT
jsonb_build_object(
'header', jsonb_build_object(
'nums', (src #>> '{header, nums}')::integer + 1,
'oldest', (src #>> '{header, oldest}')::text,
'newest', now()::text
),
'values', jsonb_build_array(
jsonb_build_object(
'value', value,
'num', (src #>> '{header, nums}')::integer + 1,
'timestamp', now()::text
)
) || (src -> 'values')::jsonb
);
$$ LANGUAGE sql;
--
-- getter functions
--
--
-- get current value
--
CREATE OR REPLACE FUNCTION value(src jsonb) RETURNS integer AS $$
SELECT (src #>> '{values, 0, value}')::integer;
$$ LANGUAGE sql;
--
-- get value by generation number
--
CREATE OR REPLACE FUNCTION value(src jsonb, num int) RETURNS integer AS $$
SELECT (src #>> ('{values, ' || num || ', value}')::text[] )::integer;
$$ LANGUAGE sql;
--
-- get value by timestamp
--
CREATE OR REPLACE FUNCTION value(src jsonb, in_t timestamptz) RETURNS integer AS $$
DECLARE
newest timestamptz;
oldest timestamptz;
n integer;
nums integer;
t timestamptz;
BEGIN
newest := (src #>> '{header,newest}')::timestamptz ;
IF in_t >= newest THEN
RETURN (src #>> '{values, 0, value}')::integer;
END IF;
oldest := (src #>> '{header,oldest}')::timestamptz ;
IF in_t < oldest THEN
RETURN null;
END IF;
nums := (src #>> '{header,nums}')::integer;
FOR n IN 0 .. nums - 1 LOOP
t = (src #>> ('{values, ' || n || ',timestamp}')::text[])::timestamptz;
IF in_t >= t THEN
RETURN (src #>> ('{values, ' || n || ',value}')::text[])::integer;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
--
-- get metadata
--
CREATE OR REPLACE FUNCTION header(src jsonb) RETURNS jsonb AS $$
SELECT (src -> 'header');
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION nums(src jsonb) RETURNS integer AS $$
SELECT (src #>> '{header,nums}')::integer;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION newest(src jsonb) RETURNS timestamptz AS $$
SELECT (src #>> '{header,newest}')::timestamptz;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION oldest(src jsonb) RETURNS timestamptz AS $$
SELECT (src #>> '{header,oldest}')::timestamptz;
$$ LANGUAGE sql;
