PostgreSQLでINSERTのみ使用してレコードの更新履歴を残すテーブル構造を実装する
今関わっている案件でレコードの更新履歴があるとうれしいなという話を聞いて、
システム設計日記
http://masuda220.jugem.jp/?eid=350
の「記録は不変( immutable )」パターンを思い出して実装してみました。
ビジネスのイベント記録の原則は、不変 ( immutable ) です。
一度記録したら、その記録を「削除」したり「変更」することを禁止する。
データベースで言えば、insert 文はＯＫだけど、update 文、 delete 文は ダメ、ということ。
テストシナリオ
案件を管理するテーブルがあり、進行に従って案件のステータスを変えて行くことを考えます。
| id | 案件番号 | 案件名 | 概要 | ステータス | 日付 |
| 1 | 10-001 | 案件A | Aシステムの開発 | 0 | 2010/04/01 |
| 2 | 10-002 | 案件B | Bシステムの開発 | 0 | 2010/04/10 |
| 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010/04/20 |
ステータスは別表があることにします。
| ステータス | 名称 |
| 0 | 見積 |
| 20 | 受注 |
| 99 | 完了 |
ここで次のような更新を加えることにします。
| id | 案件番号 | 案件名 | 概要 | ステータス | 日付 |
| 1 | 10-001 | 案件A | Aシステムの開発 | 20 | 2010/05/01 |
| 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010/05/25 |
| 2 | 10-002 | 案件B | Bシステムの開発 | 20 | 2010/07/01 |
| 3 | 10-003 | 案件C | Cシステムの開発 | 20 | 2010/07/10 |
| 2 | 10-002 | 案件B | Bシステムの開発 | 99 | 2010/07/25 |
| 3 | 10-003 | 案件C | Cシステムの開発 | 99 | 2010/08/25 |
最後に案件Aについて、過去にどのような変更があったかを取り出します。
また、過去のある時点の表を取り出してみます。
テーブル作成
INSERTのみで消し込みを行うために
- INSERTのみ行うテーブル(anken_)
- 削除情報をINSERTする削除テーブル(d_anken_)
- 削除されていないレコードだけを取り出すビュー(v_anken_)
を用意します。
実装するにあたり、次の問題があり若干定義が長くなっています。
- 過去のデータは消えずに同じテーブルに残るので主キー(id)が重複する
- → 主キー(sid)を新たに作り、idは外部キーのみに使う
- 同時に更新したときに同じ idを持つレコードが複数できてしまう（上記によりidにUNIQUE制約がかけられないので）
- → 更新対象を元テーブルではなくビューにする（ルールを使用）
- → 同時に更新された場合、後のクエリーが実行されるときには主キーが変わっているので更新に失敗し、重複更新は回避できる（・・・と思う。）
- → 更新対象を元テーブルではなくビューにする（ルールを使用）
ポイントは消し込みでビューを作る定義
- 削除テーブルに無いもの（＝有効なレコード）をSELECTしてビューを作成
--ビュー作成(v_テーブル名) CREATE VIEW v_anken_ AS SELECT * FROM anken_ WHERE NOT EXISTS (SELECT 1 FROM d_anken_ WHERE anken_.sid = d_anken_.sid);
と、ビューに対するINSERT、UPDATE、DELETEルールの定義。
- INSERTは、元のテーブルにINSERT
- UPDATEは、元のテーブルにINSERT、削除テーブルにINSERT
- DELETEは、削除テーブルにINSERT
のように、ビューにINSERT、UPDATE、DELETEを発行するとルールで適切なテーブルにINSERTが行われます。
------------------------------------------------------------ -- 共通設定（DBを作った後、最初に1回だけ実行する） ------------------------------------------------------------ --ストアドプロシージャの言語定義 CREATE PROCEDURAL LANGUAGE plpgsql; --タイムスタンプを設定するトリガー定義 CREATE FUNCTION set_timestamp() RETURNS "trigger" AS $$ begin IF TG_OP = 'INSERT' THEN new.create_at := current_timestamp; END IF; return new; end; $$ LANGUAGE plpgsql; ------------------------------------------------------------ -- anken_ テーブル ------------------------------------------------------------ --シーケンス作成(テーブル名_sid_seq) CREATE SEQUENCE anken__sid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; --テーブル作成 CREATE TABLE anken_ ( sid integer PRIMARY KEY, id integer DEFAULT currval('anken__sid_seq'::regclass) NOT NULL, ankenbangou text, kenmei text, gaiyou text, status integer DEFAULT 0, date timestamp, create_at timestamp without time zone DEFAULT now() NOT NULL, create_user integer DEFAULT 0 NOT NULL ); --削除テーブル作成 CREATE TABLE d_anken_ ( sid integer PRIMARY KEY, id integer, create_at timestamp without time zone DEFAULT now() NOT NULL, create_user integer DEFAULT 0 NOT NULL ); --シーケンス関連付け ALTER SEQUENCE anken__sid_seq OWNED BY anken_.sid; --デフォルト値設定 ALTER TABLE anken_ ALTER COLUMN sid SET DEFAULT nextval('anken__sid_seq'::regclass); --トリガー作成(trg_テーブル名) CREATE TRIGGER trg_anken_timestamp BEFORE INSERT OR UPDATE OR DELETE ON anken_ FOR EACH ROW EXECUTE PROCEDURE set_timestamp(); --インデックス作成 CREATE INDEX anken_id_index ON anken_ (id); CREATE INDEX anken_ankenbangou_index ON anken_ (ankenbangou); CREATE INDEX anken_date_index ON anken_ (date); --ビュー作成(v_テーブル名) CREATE VIEW v_anken_ AS SELECT * FROM anken_ WHERE NOT EXISTS (SELECT 1 FROM d_anken_ WHERE anken_.sid = d_anken_.sid); --ビューに対する挿入ルール作成(テーブル名_insert) CREATE RULE anken_insert AS ON INSERT TO v_anken_ DO INSTEAD ( INSERT INTO anken_ ( ankenbangou, kenmei, gaiyou, status, date, id ) VALUES ( new.ankenbangou, new.kenmei, new.gaiyou, new.status, new.date, currval('anken__sid_seq'::regclass) ); ); --ビューに対する更新ルール作成(テーブル名_update) CREATE RULE anken_update AS ON UPDATE TO v_anken_ DO INSTEAD ( INSERT INTO anken_ ( ankenbangou, kenmei, gaiyou, status, date, id ) VALUES ( new.ankenbangou, new.kenmei, new.gaiyou, new.status, new.date, old.id ); INSERT INTO d_anken_ ( sid, id, create_at ) VALUES ( old.sid, old.id, current_timestamp ); ); --ビューに対する削除ルール作成(テーブル名_delete) CREATE RULE anken_delete AS ON DELETE TO v_anken_ DO INSTEAD ( INSERT INTO d_anken_ ( sid, id, create_at ) VALUES ( old.sid, old.id, current_timestamp ); ); --ルール作成(元のテーブルはupdate, deleteできないようにする) CREATE RULE anken_update AS ON UPDATE TO anken_ DO INSTEAD NOTHING; CREATE RULE anken_delete AS ON DELETE TO anken_ DO INSTEAD NOTHING;
テーブルを消して作り直す場合は、CASCADEを付けてDROPコマンドを実行します。
--ビュー削除 DROP VIEW v_anken_ CASCADE; --削除テーブル削除 DROP TABLE d_anken_ CASCADE; --テーブル削除 DROP TABLE anken_ CASCADE;
テスト
本来はcreate_atもdateに近い値になるべきですが、今回はcreate_atの時間が詰まってます=D_
create_atに月末や年度末を指定するのが想定される使い方です。
insert into v_anken_ ( ankenbangou, kenmei, gaiyou, status, date ) values ( '10-001', '案件A', 'Aシステムの開発', '0', '2010/04/01' ); insert into v_anken_ ( ankenbangou, kenmei, gaiyou, status, date ) values ( '10-002', '案件B', 'Bシステムの開発', '0', '2010/04/10' ); insert into v_anken_ ( ankenbangou, kenmei, gaiyou, status, date ) values ( '10-003', '案件C', 'Cシステムの開発', '0', '2010/04/20' ); --更新 update v_anken_ set (status, date) = ('20', '2010/05/01') where sid = '1'; --A案件を受注 update v_anken_ set (status, date) = ('99', '2010/05/25') where sid = '4'; --A案件を完了 update v_anken_ set (status, date) = ('20', '2010/07/01') where sid = '2'; --B案件を受注 update v_anken_ set (status, date) = ('20', '2010/07/10') where sid = '3'; --C案件を受注 update v_anken_ set (status, date) = ('99', '2010/07/25') where sid = '6'; --B案件を完了 update v_anken_ set (status, date) = ('99', '2010/08/25') where sid = '7'; --C案件を完了 --案件Aの更新履歴を取得する testdb=# SELECT anken_.* FROM d_anken_ LEFT JOIN anken_ ON anken_.sid = d_anken_.sid WHERE d_anken_.id = 1 ORDER BY anken_.create_at; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 1 | 1 | 10-001 | 案件A | Aシステムの開発 | 0 | 2010-04-01 00:00:00 | 2010-04-07 20:00:27.160499 | 0 4 | 1 | 10-001 | 案件A | Aシステムの開発 | 20 | 2010-05-01 00:00:00 | 2010-04-07 20:00:40.860963 | 0 --過去のある時点の状態を取得する --2010/04/07 20:01:00 --案件Aは完了。案件B、Cは受注していない testdb=# SELECT * FROM anken_ WHERE (create_at < '2010/04/07 20:01:00') AND NOT EXISTS (SELECT 1 FROM d_anken_ WHERE anken_.sid = d_anken_.sid AND (create_at < '2010/04/07 20:01:00' )); sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 2 | 2 | 10-002 | 案件B | Bシステムの開発 | 0 | 2010-04-10 00:00:00 | 2010-04-07 20:00:27.160499 | 0 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 --2010/04/07 20:01:30 --案件Aは完了。案件Bは受注済み。案件Cは受注していない testdb=# SELECT * FROM anken_ WHERE (create_at < '2010/04/07 20:01:30') AND NOT EXISTS (SELECT 1 FROM d_anken_ WHERE anken_.sid = d_anken_.sid AND (create_at < '2010/04/07 20:01:30' )); sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 6 | 2 | 10-002 | 案件B | Bシステムの開発 | 20 | 2010-07-01 00:00:00 | 2010-04-07 20:01:17.348737 | 0
create_atを指定して遷移する途中の表が取り出せていることが分かるでしょうか。
元のテーブルの指定日時以前のレコードと、削除テーブルの指定日時以前のレコードを使って消し込みを行い、過去の状態を取り出しています。
参考までにv_anken_テーブルの遷移は以下のようになっています。
最初の追加 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 1 | 1 | 10-001 | 案件A | Aシステムの開発 | 0 | 2010-04-01 00:00:00 | 2010-04-07 20:00:27.160499 | 0 2 | 2 | 10-002 | 案件B | Bシステムの開発 | 0 | 2010-04-10 00:00:00 | 2010-04-07 20:00:27.160499 | 0 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 １件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 2 | 2 | 10-002 | 案件B | Bシステムの開発 | 0 | 2010-04-10 00:00:00 | 2010-04-07 20:00:27.160499 | 0 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 4 | 1 | 10-001 | 案件A | Aシステムの開発 | 20 | 2010-05-01 00:00:00 | 2010-04-07 20:00:40.860963 | 0 ２件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 2 | 2 | 10-002 | 案件B | Bシステムの開発 | 0 | 2010-04-10 00:00:00 | 2010-04-07 20:00:27.160499 | 0 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 ３件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 3 | 3 | 10-003 | 案件C | Cシステムの開発 | 0 | 2010-04-20 00:00:00 | 2010-04-07 20:00:27.160499 | 0 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 6 | 2 | 10-002 | 案件B | Bシステムの開発 | 20 | 2010-07-01 00:00:00 | 2010-04-07 20:01:17.348737 | 0 ４件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 6 | 2 | 10-002 | 案件B | Bシステムの開発 | 20 | 2010-07-01 00:00:00 | 2010-04-07 20:01:17.348737 | 0 7 | 3 | 10-003 | 案件C | Cシステムの開発 | 20 | 2010-07-10 00:00:00 | 2010-04-07 20:01:32.579201 | 0 ５件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 7 | 3 | 10-003 | 案件C | Cシステムの開発 | 20 | 2010-07-10 00:00:00 | 2010-04-07 20:01:32.579201 | 0 8 | 2 | 10-002 | 案件B | Bシステムの開発 | 99 | 2010-07-25 00:00:00 | 2010-04-07 20:01:42.106338 | 0 ６件目の更新 testdb=# SELECT * FROM v_anken_; sid | id | ankenbangou | kenmei | gaiyou | status | date | create_at | create_user -----+----+-------------+--------+-----------------+--------+---------------------+----------------------------+------------- 5 | 1 | 10-001 | 案件A | Aシステムの開発 | 99 | 2010-05-25 00:00:00 | 2010-04-07 20:00:58.584274 | 0 8 | 2 | 10-002 | 案件B | Bシステムの開発 | 99 | 2010-07-25 00:00:00 | 2010-04-07 20:01:42.106338 | 0 9 | 3 | 10-003 | 案件C | Cシステムの開発 | 99 | 2010-08-25 00:00:00 | 2010-04-07 20:01:53.717818 | 0
まとめ
PostgreSQLでINSERTのみ使用してレコードの更新履歴を残すテーブル構造を実装した。
- メリット
- 更新履歴を取りつつ、表向きはINSERT、UPDATE、DELETE文がそのまま使える
- トリガーでレコードにタイムスタンプ(create_at)を付けておくと任意の時点の表を取得できる
- あるレコードの更新履歴を取得できる
- デメリット
- 更新の度にレコードの主キー(sid)が変化する
- UNIQUE制約がかけられない
- テーブル定義が長くなる
SELECT一発で過去の表を取り出せるというのは、使える場面がありそうな気がします。
更新キーが変化することに目をつむれば、他はなんとかなりそうな？
テーブル定義は面倒なので一括で作成するRubyスクリプトを使っています。これは次回のネタにしたいと思います。
速度面は、ざっくりですが800万レコードほど入れた状態で追加、更新、削除ともにインデックスが張ってあれば体感で問題ないレベルだと思います。
テスト環境
- PostgreSQL 8.2.15
