2014年4月30日水曜日

PostgreSQL - ルールを使った読み込み専用テーブル、ログテーブルなどの作成2

こんにちは、稲垣です。

2週に分けてご紹介しております、PostgreSQL の「ルールを使った読み込み専用テーブル、ログテーブルなどの作成」の2回目となります。前回の記事については、以下のURLをご参照ください。

PostgreSQL - ルールを使った読み込み専用テーブル、ログテーブルなどの作成1

■その他のルールの機能

PostgreSQL の INSERT、UPDATE、DELETE 文は RETUNING 句で SELECT 文の様に結果を返せます。ルールでも全く同じように結果を返せます。RETRUNING を利用する場合、更新条件などを付けることはできません。

また、元のテーブルと異なるコラム数のデータや異なるデータ型、集約関数などの結果も返せません。

[RETURNING の利用例]

CREATE OR REPLACE RULE myinsert AS ON INSERT
   TO mytable
   DO INSTEAD
   INSERT INTO mytable_insert VALUES (
       NEW.id,
       NEW.txt,
       NEW.num,
       current_user,
       current_timestamp
   )
   RETURNING id,txt,num;

複数の SQL 文を実行させることも可能で、その場合はコマンドを () で囲みます。

[テスト用に追加するテーブルとデータ]

# CREATE TABLE mytable_counter (id INTEGER, cnt INTEGER);
CREATE TABLE
時間: 1.346 ms
# INSERT INTO mytable_counter VALUES (1, 0);
INSERT 0 1

[複数のコマンドを実行する例]

CREATE OR REPLACE RULE myinsert AS ON INSERT
   TO mytable
   DO ALSO (
   SELECT cnt FROM mytable_counter; -- この値が返される
   UPDATE mytable_counter SET cnt = cnt + 1 WHERE id = 1;
   );

挿入クエリを実行すると、更新前のカウンタの値が返されます。複数のクエリを ";" で区切って実行する場合と同じで、コラム数やデータ型の制限はありません。

[実行例]

# INSERT INTO mytable (txt, num) VALUES ('zzz', 543);
 cnt 
-----
  10
(1 行)

INSERT 0 1
時間: 0.699 ms

■削除フラグを利用した仮想削除

削除する代わりにレコードの削除フラグを立てて、アプリケーションで仮想的に削除しているアプリケーションは多いと思います。ルールを使用するとアプリケーションで対応せずに、データベースで確実に実行できます。

以下の例では削除時にレコード数が返らないことを除けば、mytable2 を普通のテーブルと同様に利用できます。

PostgreSQL 9.3 からはビューを定義すると自動的に更新可能ビューになりますが、以前の PostgreSQL ではここで紹介するようなルールなどを作成してビューを更新可能にしていました。

[削除フラグを利用したテーブル]

  • ベースとなるテーブル
  • CREATE TABLE mytable2_base (
      id SERIAL PRIMARY KEY,
      txt TEXT,
      num INTEGER,
      deleted INTEGER DEFAULT 0
    );
    
  • 実際に利用するテーブル (ビュー)
  • CREATE TABLE mytable2 (
      id INTEGER,
      txt TEXT,
      num INTEGER
    );
    

[削除フラグを利用するルール]

同じテーブルに SELECT ルールを設定すると、再帰でエラーとなります。

CREATE OR REPLACE RULE "_RETURN" AS ON SELECT
   TO mytable2
   DO INSTEAD SELECT id, txt, num FROM mytable2_base WHERE deleted=0;

CREATE OR REPLACE RULE mytable2_insert AS ON INSERT
   TO mytable2
   DO INSTEAD INSERT INTO mytable2_base (
      txt,
      num
   ) VALUES (
      NEW.txt,
      NEW.num
   );

CREATE OR REPLACE RULE mytable2_update AS ON UPDATE
   TO mytable2
   DO INSTEAD UPDATE mytable2_base SET
       txt = NEW.txt,
       num = NEW.num
   WHERE OLD.id = id AND deleted = 0;

CREATE OR REPLACE RULE mytable2_delete AS ON DELETE
   TO mytable2
   DO INSTEAD UPDATE mytable2_base SET
      deleted = 1
   WHERE OLD.id = id;

[テストデータの挿入とクエリの実行]

# INSERT INTO mytable2 (txt, num) VALUES ('abc', 123);
INSERT 0 1
時間: 0.733 ms
# INSERT INTO mytable2 (txt, num) VALUES ('abc', 123);
INSERT 0 1
時間: 0.905 ms
# INSERT INTO mytable2 (txt, num) VALUES ('abc', 123);
INSERT 0 1
時間: 0.353 ms
# SELECT * FROM mytable2;
 id | txt | num 
----+-----+-----
  1 | abc | 123
  2 | abc | 123
  3 | abc | 123
(3 行)

時間: 0.392 ms
# SELECT * FROM mytable2_base;
 id | txt | num | deleted 
----+-----+-----+---------
  1 | abc | 123 |       0
  2 | abc | 123 |       0
  3 | abc | 123 |       0
(3 行)

時間: 0.308 ms
# UPDATE mytable2 SET txt='XYZ' WHERE id=2;
UPDATE 1
時間: 0.517 ms
# SELECT * FROM mytable2;
 id | txt | num 
----+-----+-----
  1 | abc | 123
  3 | abc | 123
  2 | XYZ | 123
(3 行)

時間: 0.342 ms
# DELETE FROM mytable2 WHERE id=3;
DELETE 0
時間: 0.487 ms
# SELECT * FROM mytable2;
 id | txt | num 
----+-----+-----
  1 | abc | 123
  2 | XYZ | 123
(2 行)

時間: 0.331 ms
# SELECT * FROM mytable2_base;
 id | txt | num | deleted 
----+-----+-----+---------
  1 | abc | 123 |       0
  2 | XYZ | 123 |       0
  3 | abc | 123 |       1
(3 行)

時間: 0.317 ms

DELETE クエリは UPDATE 文に書き換えられているので、削除したレコード数が常に 0 になっています。これには注意が必要です。

■その他の利用方法

PostgreSQL の関数も利用できるので、クエリを実行したクライアントの IP アドレス (inet_current_addr()) などを記録することも可能です。この記事では挿入した回数を記録する例も紹介しました。

クエリをログファイルに記録して集計する方法もありますが、このような統計情報をテーブルに記録しリアルタイムに利用する事も可能です。クライアント IP やユーザ毎のクエリ数などを集計するには便利です。

■ルールの削除

最後にルールの削除方法を紹介します。

[書式]

DROP RULE rule_name ON table_name;

ルールが定義されたテーブルを削除する場合に CASCADE を付けると、シークエンスなどと同様にルールも削除されます。

[書式]

DROP TABLE table_name CASCADE;

■まとめ

ルールはトリガーほど複雑な操作は行えませんが、クエリの書き換えであるため多くの場合はトリガーよりも効率よく実行できます。

使い方次第で便利に使えますが他の専用機能、例えば CHECK 制約で十分な場合はそちらを利用する方が良いでしょう。制約が変わってしまう場合にはルールを使うと良いと思います。

複雑なルールや複雑な処理を行うルールを定義すると、パフォーマンスにも影響します。利用する場合は、性能要件に見合うか確認しながら利用して下さい。

38.5. Rules and Privileges (PostgreSQL 9.3マニュアル) でルールと権限について解説しています。セキュリティ要件が厳しい場合、こちらのページを必ず参照するようにして下さい。

ルールは更新可能ビューの作成によく利用されていましたが、PostgreSQL 9.3 からは単純なビューであればデフォルトで更新可能になりました。ルールを利用する場面は少なくなりますが、トリガーの代わりに利用すると良いでしょう。

[参考 URL]

http://www.postgresql.org/docs/9.3/static/rules.html

http://www.postgresql.org/docs/9.3/static/sql-revoke.html

http://www.postgresql.org/docs/9.3/static/functions-info.html

http://www.postgresql.org/docs/9.3/static/rules-privileges.html

0 件のコメント:

コメントを投稿