2012年5月2日水曜日

PostgreSQL のルールを利用した書き込み専用データベースの構築

こんにちは。サイオステクノロジー 渡辺です。

今回は PostgreSQL のルールを利用した書き込み専用データベースを紹介します。

はじめに

PostgreSQL には他の RDBMS には見られないルールがあります。ルールはトリガと同様にクエリ発行時に自動処理を行う為の仕組みです。ルールシステムは初期の Postgres からサポートされ、現在の PostgreSQL 全てが持っている機能です。ルールを用いると簡単にサーバ側の処理を定義できます。ルールとトリガは似ています。しかし、全く別の機能です。PostgreSQL のオンラインマニュアルでは「ルールシステム」として解説されています。

今回はルールを利用して、MySQL のアーカイブストレージエンジンのようなテーブルを作成してみます。


ルールとトリガ

ルールはクエリを書き換えて動作を変更します。トリガはクエリの動作前・動作後にストアドプロシージャを呼びます。トリガでもルールと同じような動作を定義する事もできます。しかし、ルールはクエリを書き換えるのでストアドプロシージャより簡潔に動作を定義できる場合があります。

ルールが適用されるタイミング
  • 書き換え対象のクエリが発行された時

トリガ起動のタイミング
  • INSERT、UPDATE または DELETE 操作の前後
  • 行を変更する度
  • SQL 文ごと

SQL92 には更新可能なビューの定義がありますが PostgreSQL はサポートしていません。ルールもトリガも更新可能なビューの作成にも利用できます。ルールの場合、ビューに対する操作をビューの元となったテーブルに対する操作のクエリに書き換えます。トリガの場合、INSTEAD OFトリガを定義してビューでなくテーブルを操作します。

テーブルとビュー定義
CREATE TABLE test (id SERIAL NOT NULL, t TEXT);
CREATE VIEW test_view AS SELECT * FROM test;

test_view に挿入すると以下の様なエラーが発生します。(PostgreSQL 9.1)
# INSERT INTO test_view (t) VALUES ('aaa');
ERROR:  cannot insert into view "test_view"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.

このエラーメッセージの通り、条件の無い "ON INSERT DO INSTED”ルールか”INSTEAD OF INSERT" トリガーが必要です。


ルールを利用した更新可能なビューの作成

ルールを利用した更新可能ビュー

挿入ルール
CREATE RULE test_view_insert AS ON INSERT TO test_view DO INSTEAD INSERT INTO test (t) VALUES (NEW.t);

更新ルール
CREATE RULE test_view_update AS ON UPDATE TO test_view DO INSTEAD UPDATE test SET t = NEW.t  WHERE id = OLD.id;

削除ルール
CREATE RULE test_view_delete AS ON DELETE TO test_view DO INSTEAD DELETE FROM test WHERE id = OLD.id;



トリガを利用した更新可能なビューの作成

test_view に対して同じ動作をするトリガとストアドプロシージャの定義は以下の通りです。

トリガを利用した更新可能ビュー
CREATE OR REPLACE FUNCTION test_view_dml() RETURNS TRIGGER AS $body$
  BEGIN
    -- 挿入
    IF TG_OP = 'INSERT' THEN
        INSERT INTO test (t) VALUES (NEW.t);
        RETURN NEW;
    -- 更新
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE test SET id=NEW.id, t=NEW.t WHERE id=OLD.id;
        RETURN NEW;
    -- 削除 
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM test WHERE id=OLD.id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$body$ LANGUAGE plpgsql;

-- 古いトリガーの削除(該当しない場合は無視ください)
DROP TRIGGER test_view_upd ON test_view;

-- トリガの登録
CREATE TRIGGER test_view_upd INSTEAD OF INSERT OR UPDATE OR DELETE ON test_view FOR EACH ROW EXECUTE PROCEDURE test_view_dml();


ルールとトリガの動作確認

ルールで定義している更新可能ビューが動作しているか確かめてみます。
user@[local] ~=# -- 挿入ルール
user@[local] ~=# CREATE RULE test_view_insert AS ON INSERT TO test_view DO INSTEAD INSERT INTO test (t) VALUES (NEW.t);
CREATE RULE
時間: 0.848 ms
user@[local] ~=# -- 更新ルール
user@[local] ~=# CREATE RULE test_view_update AS ON UPDATE TO test_view DO INSTEAD UPDATE test SET t = NEW.t WHERE id = OLD.id;
CREATE RULE
時間: 0.481 ms
user@[local] ~=# -- 削除ルール
user@[local] ~=# CREATE RULE test_view_delete AS ON DELETE TO test_view DO INSTEAD DELETE FROM test WHERE id = OLD.id;
CREATE RULE
時間: 0.802 ms

ルールの登録が完了しました。念の為に test_view ビューの定義を確認してみます。
user@[local] ~=# \d test_view
 ビュー "public.test_view"
 カラム |   型    | 修飾語
--------+---------+--------
 id     | integer |
 t      | text    |
ビュー定義:
 SELECT test.id, test.t FROM test;
ルール:
 test_view_delete AS
    ON DELETE TO test_view DO INSTEAD  DELETE FROM test
  WHERE test.id = old.id
 test_view_insert AS
    ON INSERT TO test_view DO INSTEAD  INSERT INTO test (t)
  VALUES (new.t)
 test_view_update AS
    ON UPDATE TO test_view DO INSTEAD  UPDATE test SET id = new.id, t = new.t
  WHERE test.id = old.id
問題なく登録されています。現在の内容を確認してから、実際にデータをビューに挿入してみます。
user@[local] ~=# SELECT * FROM test_view;
 id |  t  
----+-----
  5 | xyz
(1 行)

時間: 0.515 ms
user@[local] ~=# INSERT INTO test_view (t) VALUES ('abc');
INSERT 0 1
時間: 0.441 ms
user@[local] ~=# INSERT INTO test_view (t) VALUES ('abc');
INSERT 0 1
時間: 0.863 ms
user@[local] ~=# SELECT * FROM test_view;
 id |  t  
----+-----
  5 | xyz
  6 | abc
  7 | abc
(3 行)

時間: 0.366 ms
INSERT 文で test_view へ 2行挿入し、SELECT 文で追加された行が存在する事が確認できました。次に更新を試します。
user@[local] ~=# UPDATE test_view SET t = 'ABC' WHERE id = 6;
UPDATE 1
時間: 0.614 ms
user@[local] ~=# SELECT * FROM test_view;
 id |  t  
----+-----
  5 | xyz
  7 | abc
  6 | ABC
(3 行)

時間: 0.403 ms
id が 6の行のデータが更新されました。最後に削除を試します。
user@[local] ~=# DELETE FROM test_view WHERE id = 5;
DELETE 1
時間: 0.672 ms
user@[local] ~=# SELECT * FROM test_view;
 id |  t  
----+-----
  7 | abc
  6 | ABC
(2 行)

時間: 0.398 ms
user@[local] ~=#
id が 5の行が削除されました。意図通りに test_view を通じて test テーブルを更新できている事が確認できました。

同様の仕組みはトリガを利用する事もできます。この記事を書くためにインターネットを検索してみると、更新可能なビューの作成にはルールよりトリガを使うべきだ、とする意見が多く見られました。しかし、トリガとストアドプロシージャを利用する場合、ストアドプロシージャを定義しトリガを定義する 2つのステップが必要です。ルールの場合、ルールを定義するだけなので、トリガーより簡単に更新可能なビューを作成できる言えます。

速度が速い事もルールのメリットです。ルールはクエリの書き換えである為、ストアドプロシージャを実行するよりも効率良く実行できます。簡単な PHP スクリプト作って確かめてみましょう。次の PHP スクリプトは test_view にランダムな数値を挿入するスクリプトです。

bench_updatable_view.php
---
<?php
$db = pg_pconnect('host=localhost port=5491 user=user');
$sql = 'INSERT INTO test_view (t) VALUES ($1);';
$md5 = $sql;
// プリペアードクエリクエリでクエリを実行
if (!@pg_execute($md5, array(mt_rand(0,9999)))) {
    pg_prepare($md5, $sql);
    if (!pg_execute($md5, array(mt_rand(0,9999)))) {
        die('Query failed');
    }
}
echo 'OK';
---

この PHP スクリプトを Linux 2.6, Apache 2.2, PHP 5.3, PostgresSQL 9.1 の環境で ab コマンドを利用してベンチマークを取得してみました。
ab -c 10 -n 1000 http://localhost/bench_updatable_view.php

ストアドプロシージャの場合
---
Concurrency Level:      10
Time taken for tests:   4.007 seconds
Complete requests:      10000
Failed requests:        0
Write errors:           0
Total transferred:      1940000 bytes
HTML transferred:       20000 bytes
Requests per second:    2495.49 [#/sec] (mean)
Time per request:       4.007 [ms] (mean)
Time per request:       0.401 [ms] (mean, across all concurrent requests)
Transfer rate:          472.78 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       1
Processing:     1    4   0.9      4      11
Waiting:        1    4   0.9      4      11
Total:          1    4   0.9      4      11

Percentage of the requests served within a certain time (ms)
  50%      4
  66%      4
  75%      4
  80%      5
  90%      5
  95%      6
  98%      7
  99%      7
 100%     11 (longest request)
---

ルールの場合
---
Concurrency Level:      10
Time taken for tests:   3.685 seconds
Complete requests:      10000
Failed requests:        0
Write errors:           0
Total transferred:      1940000 bytes
HTML transferred:       20000 bytes
Requests per second:    2713.94 [#/sec] (mean)
Time per request:       3.685 [ms] (mean)
Time per request:       0.368 [ms] (mean, across all concurrent requests)
Transfer rate:          514.17 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       1
Processing:     1    4   1.0      3      23
Waiting:        1    3   0.9      3      10
Total:          1    4   1.0      3      23

Percentage of the requests served within a certain time (ms)
  50%      3
  66%      4
  75%      4
  80%      4
  90%      5
  95%      5
  98%      6
  99%      6
 100%     23 (longest request)
---

多少のバラツキはありましたが、総じて 1割程度ルールの方が良いパフォーマンスでした。これはルールがクエリの書き換えであり、ストアドプロシージャを実行しない分、速く処理できる事が理由です。ルールで十分な場合、ルールを利用する方が性能的には有利であることがわかります。


アーカイブ専用データベースの作成

ルールがクエリの書き換えと理解していれば、INSERT 以外のクエリを無視すれば、アーカイブ専用のデータベースを作れる事が理解できます。トリガとストアドプロシージャを使っても同様の動作を実現できますが、ルールを利用した方が良いパフォーマンスが期待できます。

更新可能なビューを作成した場合と同じく、INSTEAD OF を利用して挿入以外の操作をできなくします。

test テーブルに対する更新と削除を行えなくするルール
-- 更新ルール
CREATE RULE test_update AS ON UPDATE TO test DO INSTEAD NOTHING;
-- 削除ルール
CREATE RULE test_delete AS ON DELETE TO test DO INSTEAD NOTHING;
この二つのルールを定義するだけで test テーブルへの更新も削除もできなくなります。


まとめ

テーブルへのアクセス権限は GRANT 文で制御可能です。しかし、アーカイブ専用のテーブルの場合、テーブル定義で更新もできなくする方が、設定漏れなどの場合にも対処できるのでより安全です。しかもルールの定義は非常に簡潔です。今回は解説しませんでしたが、ルールを利用してテーブルの更新履歴を保存する事もできます。複数のクエリを発行する事も可能であるため、ストアドプロシージャで出来る事の多くがルールで定義できます。

PostgreSQL のルールシステムは非常に柔軟で強力です。PostgreSQL のビューはルールを利用して実装されています。ストアドプロシージャより効率良く実行できる事も魅力です。ルールは他の RDBMS には無い見慣れない機能かも知れませんが、PostgreSQL を使いこなすエンジニアを目指すには欠かせない機能です。まだルールを使った事がない方は、これを機会にトリガの代わりにルールを利用してみてはいかがでしょうか。





参考文献

第36章トリガ
http://www.postgresql.jp/document/9.1/html/triggers.html

第37章ルールシステム
http://www.postgresql.jp/document/9.1/html/rules.html

第39章 PL/pgSQL
http://www.postgresql.jp/document/9.1/html/plpgsql.html



記事の作成にあたり、エレクトロニック・サービス・イニシアチブ社 大垣 靖男様に執筆協力をいただきました。

0 件のコメント:

コメントを投稿