2014年4月24日木曜日

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

PostgreSQL には他の RDBMS にはあまり見られないルールという仕組みがあります。ルールはクエリを書き換える仕組みです。トリガーでできる事の一部がルールで実現できます。

ルールは PostgreSQL に特徴的な仕組みで、ルールを直接使用しなくてもビューはルールとして実装されています。ルールはストアドプロシージャを作らなくても簡単にクエリ動作を変更でき、便利な場合もあります。

今回は2週に分けて、ルールを使用した読み込み専用テーブルのログテーブルの作成を紹介します。

記事の執筆には Linux 上の PostgreSQL 9.3(git版) を利用していますが、古い PostgreSQL でも同様に実行できます。

■ルールの書式

ルールは SELECT、INSERT、UPDATE、DELETE 文に利用できます。

[ルール定義の書式]

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
name ルール名
event イベント名 (INSERT, UPDATE, DELETE)
table 対象のテーブル名
condition WHERE 句の条件
command SQL コマンド

SELECT 文の場合、name には "_RETURN" が必要になります。他を指定した場合、次のエラーが発生します。

ERROR:  view rule for "myview" must be named "_RETURN"

DO の後には INSTED SELECT が必ず必要になります。他を指定した場合、次のエラーが発生します。

ERROR:  rules on SELECT must have action INSTEAD SELECT

ルールには様々な制約がありますが、エラー文を読めば何が問題であるか直ぐに解ると思います。

■■SELECT 文のルール

SELECT 文のルールはビューの作成と変わりません。ルールで作成する方が簡単なので実際には CREATE VIEW コマンドを使うべきですが、ルールを使ったビューを作成してみます。

[テーブル定義]

  • 元のテーブル
  • CREATE TABLE mytable (
      id SERIAL,
      txt TEXT,
      num INTEGER
    );
    
  • ビューとなるテーブル
  • CREATE TABLE myview (
      id INTEGER,
      txt TEXT
    );
    

[ルール定義]

CREATE OR REPLACE RULE "_RETURN" AS ON SELECT
   TO myview
   DO INSTEAD SELECT id, txt FROM mytable;

これはビューを定義した場合に PostgreSQL が内部的に作成するルールと同等です。実際、myview はビューとして認識されます。

[テーブル定義]

# \d
                リレーションの一覧
 スキーマ |      名前      |     型     | 所有者  
----------+----------------+------------+---------
 public   | mytable        | テーブル   | postgres
 public   | mytable_id_seq | シーケンス | postgres
 public   | myview         | ビュー     | postgres
(3 行)

動作確認する為にテスト用のデータを挿入します。

[テストデータを 4 レコード挿入]

INSERT INTO mytable (txt, num) VALUES ('abc', 123);
INSERT INTO mytable (txt, num) VALUES ('abc', 123);
INSERT INTO mytable (txt, num) VALUES ('abc', 123);
INSERT INTO mytable (txt, num) VALUES ('abc', 123);

ルールで作成したビューにアクセスしてみます。

[myview の中身]

SELECT * FROM myview;
 id | txt 
----+-----
  1 | abc
  2 | abc
  3 | abc
  4 | abc
(4 行)

以下の SQL 文を実行してビューを作成した場合と、同様の結果が得られている事が分かります。

CREATE VIEW myview AS SELECT id, txt FROM mytable;

■■SELECT を制限テーブルの作成

ルールで SELECT できないテーブルを作成するには、以下の SQL 文を実行すれば良いと思うかも知れません。

CREATE OR REPLACE RULE "_RETURN" AS ON SELECT
  TO mytable
  DO INSTEAD NOTHING;

しかし、SELECT 文のルールには INSTEAD SELECT が必須なので、この定義は次のエラーで失敗します。

ERROR:  rules on SELECT must have action INSTEAD SELECT

SELECT できないテーブルを作成するには、ユーザーから SELECT 権限を削除する必要があります。

REVOKE SELECT ON mytable FROM username;

■■INSERT、UPDATE、DELETE できないテーブルの作成

ルールは権限を管理する仕組みではないので、関数などからアクセスできてしまう場合もあります。実際にはユーザから権限を削除すべきですが、ルールを使用して制限することも可能です。

[INSERT を無効化するルール]

CREATE OR REPLACE RULE myinsert AS ON INSERT
   TO mytable
   DO INSTEAD NOTHING;

挿入を試みても挿入できません。

# INSERT INTO mytable (txt, num) VALUES ('abc', 123);
INSERT 0 0

同様に UPDATE, DELETE 文に DO INSTEAD NOTHING を設定すると、ルールによって更新系のクエリ実行を制限することができます。

■■条件に一致しない場合に挿入しない

完全に更新系クエリを制限するには権限を削除すれば良いのですが、不正な値を排除したい場合などにはルールを利用できます。テーブル定義時の CHECK 制約でも同じ事が可能ですが、今回はルールを利用します。

[num が 1000 以上の場合は挿入しないルール]

CREATE OR REPLACE RULE myinsert AS ON INSERT
   TO mytable WHERE num >= 1000
   DO INSTEAD NOTHING;

[実行例]

# INSERT INTO mytable (txt, num) VALUES ('abc', 100);
INSERT 0 1
時間: 0.451 ms
# INSERT INTO mytable (txt, num) VALUES ('abc', 1000);
INSERT 0 0
時間: 0.246 ms

■■条件に一致しない場合、挿入せずログを取得

DO INSTEAD NOTHING の代わりに不正なクエリをログする事も可能です。

[挿入をログするテーブル]

CREATE TABLE mytable_insert (
  id INTEGER,
  txt TEXT,
  num INTEGER,
  log_user TEXT, -- 現在のユーザ
  log_time TIMESTAMP -- 現在の時刻
);

INSERT クエリではルール定義に新しい値を指定する NEW が利用できます。例えば、id コラムは次の定義の様に NEW.id と指定しないとエラーになります。

[num が 1000 以上の場合は挿入せずにログする]

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

[実行例]

# INSERT INTO mytable (txt, num) VALUES ('abc', 100);
INSERT 0 1
時間: 0.535 ms
# INSERT INTO mytable (txt, num) VALUES ('abc', 1000);
INSERT 0 0
時間: 0.505 ms
# SELECT * FROM mytable_insert;
 id | txt | num  | log_user |          log_time          
----+-----+------+----------+----------------------------
  7 | abc | 1000 | postgres | 2013-09-24 19:02:47.443808
(1 行)

時間: 0.318 ms

この実行例から分かるように num が 100 の場合は mytable への挿入が成功し、1000 の場合は失敗し mytable_invalid にレコードが挿入されていることが分かります。

■■全ての挿入をログする

前の例では不正な挿入のみを記録しましたが、全ての挿入をログする事も可能です。mytable への挿入と一緒に mytable_insert にも挿入するよう DO ALSO を利用します。

[全ての挿入をログするルール]

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

[実行例]

# SELECT * FROM mytable_insert;
 id | txt | num  | log_user |          log_time          
----+-----+------+----------+----------------------------
 10 | abc | 1000 | postgres | 2013-09-24 19:15:29.599345
 12 | abc | 2000 | postgres | 2013-09-24 19:15:35.639485
(2 行)

時間: 0.319 ms

■■更新を自動的にログする

挿入の場合と同様に更新データを全てログする事も可能です。挿入の場合は NEW が利用できましたが、更新の場合は OLD が利用できます。

[更新をログするテーブル]

CREATE TABLE mytable_update (
  id INTEGER,
  old_txt TEXT,
  new_txt TEXT,
  old_num INTEGER,
  new_num INTEGER,
  log_user TEXT, -- 現在のユーザ
  log_time TIMESTAMP -- 現在の時刻
);

[変更があった場合の更新をログするルール]

CREATE OR REPLACE RULE myupdate AS ON UPDATE
   TO mytable WHERE NEW.txt <> OLD.txt OR NEW.num <> OLD.num
   DO ALSO
   INSERT INTO mytable_update VALUES (
       OLD.id, -- id は更新されないと仮定。OLD か NEW のどちらかの指定が必須
       NEW.txt,
       OLD.txt,
       NEW.num,
       OLD.num,
       current_user,
       current_timestamp
   );

[実行例]

# UPDATE mytable SET txt = 'xyz', num = 5678 WHERE num = 2000;
UPDATE 1
時間: 20.724 ms
# SELECT * FROM mytable_update;
 id | old_txt | new_txt | old_num | new_num | log_user |         log_time          
----+---------+---------+---------+---------+----------+---------------------------
 11 | xyz     | abc     |    5678 |    2000 | postgres | 2013-09-24 19:26:27.03912
(1 行)

時間: 0.342 ms

■■削除を自動的にログする

更新のログとほとんど同じです。

[削除をログするテーブル]

CREATE TABLE mytable_delete (
  id INTEGER,
  txt TEXT,
  num INTEGER,
  log_user TEXT, -- 現在のユーザ
  log_time TIMESTAMP -- 現在の時刻
);

[削除をログするルール]

CREATE OR REPLACE RULE mydelete AS ON DELETE
   TO mytable
   DO ALSO
   INSERT INTO mytable_delete VALUES (
       OLD.id,
       OLD.txt,
       OLD.num,
       current_user,
       current_timestamp
   );

[実行例]

# DELETE FROM mytable WHERE num >= 1000;
DELETE 3
時間: 0.778 ms
# SELECT * FROM mytable_delete;
 id | txt | num  | log_user |          log_time          
----+-----+------+----------+----------------------------
  8 | abc | 1000 | postgres | 2013-09-24 19:35:31.586597
  9 | abc | 1000 | postgres | 2013-09-24 19:35:31.586597
 11 | xyz | 5678 | postgres | 2013-09-24 19:35:31.586597
(3 行)

時間: 0.315 ms

削除されたレコード全てが記録されていることが分かります。

本記事の続きは 4月30日に公開予定です。

[参考 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 件のコメント:

コメントを投稿