VIEWとRULE

2003-12-27T11:00+09:00 matsu

仮想的なテーブルVIEWを使用すると頻繁に使用する複雑なSQL文の記述が楽になったりする.RULEはVIEWに対するINSERT,UPDATE,DELETEを行なうのに使用する.

VIEW作成
RULEとは
RULEを使用してVIEWを更新,削除できるようにする
VIEWによるアクセス権限の設定
VIEWとRULEの削除

VIEWを作成する基本形は以下である.

CREATE VIEW ビュー名 AS SELECT文

これによりSELECT文の結果を指定したビュー名のテーブルとして使用できる.以下の例はresult_linpackの結果ベスト10を該当するクラスタ名とともに表示するVIEWを作成するものである.

benchmark=> SELECT cluster.name, result_linpack.*
benchmark-> FROM cluster JOIN result_linpack
benchmark-> ON cluster.id = result_linpack.cluster_id
benchmark-> ORDER BY gflops 
benchmark-> LIMIT 10;
 name |   n   | nb  | cluster_id | gflops | exec_date  
------+-------+-----+------------+--------+------------
 hoge | 10000 | 120 |          1 |  3.242 | 
 hoge | 11000 | 120 |          1 |  3.467 | 
 hoge | 12000 | 120 |          1 |   3.64 | 
 hoge | 13000 | 120 |          1 |  3.904 | 
 foo  | 20000 |  20 |          3 |  3.912 | 
 hoge | 14000 | 120 |          1 |  4.059 | 
 var  | 20000 |  20 |          4 |  4.524 | 2003-12-13
 fuga | 20000 |  20 |          2 |  4.529 | 
 foo  | 11000 | 120 |          3 |  4.827 | 
 foo  | 12000 | 120 |          3 |  5.196 | 
(10 rows)

benchmark=> CREATE VIEW linpack_best10     
benchmark-> AS 
benchmark-> SELECT cluster.name, result_linpack.*
benchmark-> FROM cluster JOIN result_linpack
benchmark-> ON cluster.id = result_linpack.cluster_id
benchmark-> ORDER BY gflops
benchmark-> LIMIT 10;
CREATE

benchmark=> SELECT * FROM linpack_best10;
 name |   n   | nb  | cluster_id | gflops | exec_date  
------+-------+-----+------------+--------+------------
 hoge | 10000 | 120 |          1 |  3.242 | 
 hoge | 11000 | 120 |          1 |  3.467 | 
 hoge | 12000 | 120 |          1 |   3.64 | 
 hoge | 13000 | 120 |          1 |  3.904 | 
 foo  | 20000 |  20 |          3 |  3.912 | 
 hoge | 14000 | 120 |          1 |  4.059 | 
 var  | 20000 |  20 |          4 |  4.524 | 2003-12-13
 fuga | 20000 |  20 |          2 |  4.529 | 
 foo  | 11000 | 120 |          3 |  4.827 | 
 foo  | 12000 | 120 |          3 |  5.196 | 
(10 rows)

以後linpack_best10というビューはテーブルと同様にSELECTできる.

RULEとは

作成したビューlinpack_best10を確認してみる.

benchmark=> \d linpack_best10
           View "linpack_best10"
   Column   |       Type       | Modifiers 
------------+------------------+-----------
 name       | text             | 
 n          | integer          | 
 nb         | integer          | 
 cluster_id | integer          | 
 gflops     | double precision | 
 exec_date  | date             | 
View definition: SELECT "cluster".name, result_linpack.n, result_linpack.nb,
result_linpack.cluster_id, result_linpack.gflops, result_linpack.exec_date
FROM ("cluster" JOIN result_linpack ON (("cluster".id = result_linpack.cluster_id)))
ORDER BY result_linpack.gflops LIMIT 10;

View definitionで始まる部分は表示上の理由で改行したが,実際は一行である.で,この一行がVIEWの仕組みである.linpack_best10に対するSELECT文が発行されると,この一行に記述されているSQL文に置き換えられ実行される.これがRULEである.CREATE VIEWするとRULEが作成される.

CREATE RULE ルール名 AS ON 操作 TO テーブル名 DO INSTEAD SQL文

これにより指定したテーブルに対して指定した操作(SELECT,UPDATE,DELETE等)が発行されると,SQL文に書き換えられて実行される.linpack_best10をCREATE VIEWした際には内部的に以下のRULEが作成されると考えることができる(※).

CREATE RULE "_linpack_best10"
AN ON SELECT TO linpack_best10
DO INSTEAD ...

...で省略した部分には先のView definitionで始まる行にあるSELECT文が入る.

※ただしpg_rulesにエントリは追加されない.

benchmark=> SELECT * FROM pg_rules;
 tablename | rulename | definition 
-----------+----------+------------
(0 rows)

RULEを使用してVIEWを更新,削除できるようにする

前節の内容から,VIEWに対しては更新,削除ができないことが想像できる.実際

benchmark=> UPDATE linpack_best10
benchmark-> SET exec_date='2003-12-23'
benchmark-> WHERE exec_date IS NULL;
ERROR:  Cannot update a view without an appropriate rule

である.VIEWを対象にしてRULEを作成することで,VIEWに対して更新や削除ができるようになる.linpack_best10のexec_dateをUPDATEできるようにするためにRULEを作成する.

benchmark=> CREATE RULE "_linpack_best10_exec_date_upd"
benchmark-> AS ON UPDATE TO linpack_best10
benchmark-> DO INSTEAD (
benchmark(> UPDATE result_linpack
benchmark(> SET exec_date = new.exec_date
benchmark(> WHERE n = old.n AND nb = old.nb AND cluster_id = old.cluster_id);
CREATE

これはlinpack_best10に対してUPDATEが実行された際に代わりに実行するSQL文を一つ指定している(※).実際に更新するテーブルはresult_linpackとしたので,実行するUPDATEのWHERE句にはキーn,nb,cluster_idを使用した.oldとnewは予約後で,それぞれ今の値,新しい値を指すのに使用する.では実際にUPDATEしてみる.

benchmark=> SELECT * FROM linpack_best10;
 name |   n   | nb  | cluster_id | gflops | exec_date  
------+-------+-----+------------+--------+------------
 hoge | 10000 | 120 |          1 |  3.242 | 
 hoge | 11000 | 120 |          1 |  3.467 | 
 hoge | 12000 | 120 |          1 |   3.64 | 
 hoge | 13000 | 120 |          1 |  3.904 | 
 foo  | 20000 |  20 |          3 |  3.912 | 
 hoge | 14000 | 120 |          1 |  4.059 | 
 var  | 20000 |  20 |          4 |  4.524 | 2003-12-23
 fuga | 20000 |  20 |          2 |  4.529 | 
 foo  | 11000 | 120 |          3 |  4.827 | 
 foo  | 12000 | 120 |          3 |  5.196 | 
(10 rows)

benchmark=> UPDATE linpack_best10
benchmark-> SET exec_date = '2003-12-27'
benchmark-> WHERE n = 10000 AND nb = 120 AND cluster_id = 1;
UPDATE 0

benchmark=> SELECT * FROM linpack_best10;
 name |   n   | nb  | cluster_id | gflops | exec_date  
------+-------+-----+------------+--------+------------
 hoge | 10000 | 120 |          1 |  3.242 | 2003-12-27
 hoge | 11000 | 120 |          1 |  3.467 | 
 hoge | 12000 | 120 |          1 |   3.64 | 
 hoge | 13000 | 120 |          1 |  3.904 | 
 foo  | 20000 |  20 |          3 |  3.912 | 
 hoge | 14000 | 120 |          1 |  4.059 | 
 var  | 20000 |  20 |          4 |  4.524 | 2003-12-23
 fuga | 20000 |  20 |          2 |  4.529 | 
 foo  | 11000 | 120 |          3 |  4.827 | 
 foo  | 12000 | 120 |          3 |  5.196 | 
(10 rows)

UPDATE 0と出力されるのは,実際に更新するのはresult_linpackであってlinpack_best10ではないためだろうか.

※ DO INSTEADで複数のSQL文を指定する場合にはそれらを()で囲う.

VIEWによるアクセス権限の設定

VIEWを使用すると,詳細なアクセス権限の設定が可能となる.例えばresult_linpackのアクセス権が以下だとする.

benchmark=> \z
Access privileges for database "benchmark"
     Table      | Access privileges 
----------------+-------------------
 cluster        | 
 linpack_best10 | 
 result_linpack | 
 result_netpipe | 
(4 rows)

この場合,作成者以外はresult_linpackに対するアクセス権が全くない.ここでlinpack_best10に対してGRANTすると,result_linpackにはアクセス権がないが,linpack_best10に該当するレコードだけはlinpack_best10を通して参照することができる.

benchmark=> GRANT SELECT ON linpack_best10 TO public;
GRANT

benchmark=> \z
Access privileges for database "benchmark"
     Table      | Access privileges  
----------------+--------------------
 cluster        | 
 linpack_best10 | {=r,matsu=arwdRxt}
 result_linpack | 
 result_netpipe | 
(4 rows)

これで,result_linpackへのアクセス権を制限したままで,linpack_best10のみのSELECT権を全ユーザに与えることができる.もう一つ,例としてpg_userがある.一般ユーザはpg_shadowを参照することができないが,pg_shadowを参照するビューpg_userは参照できる.

benchmark=> SELECT * FROM pg_shadow;
ERROR:  pg_shadow: Permission denied.
benchmark=> SELECT usename, passwd FROM pg_user;
 usename  |  passwd  
----------+----------
 postgres | ********
 matsu    | ********
(2 rows)

passwdが'********'となっている.これは特定の列を表示したくない場合の対処例である(※).確認してみた(表示上の理由で一部改行している).

benchmark=> \d pg_user
          View "pg_user"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | integer | 
 usecreatedb | boolean | 
 usetrace    | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
View definition: SELECT pg_shadow.usename, pg_shadow.usesysid,
pg_shadow.usecreatedb, pg_shadow.usetrace, pg_shadow.usesuper,
pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil
FROM pg_shadow;

同様にしてRULEを作成すれば,更新,挿入,削除についても詳細にアクセス権を設定できる.

※ もちろんビュー作成時にその列を作成しないという方法もある.

VIEWとRULEの削除

VIEWやRULEを削除するには,DROPを使用する.

benchmark=> SELECT rulename FROM pg_rules;
           rulename            
-------------------------------
 _linpack_best10_exec_date_upd
(1 row)

benchmark=> DROP RULE _linpack_best10_exec_date_upd;
DROP
benchmark=> SELECT rulename FROM pg_rules;
 rulename 
----------
(0 rows)

benchmark=> SELECT viewname FROM pg_views WHERE viewname = 'linpack_best10';
    viewname    
----------------
 linpack_best10
(1 row)

benchmark=> DROP VIEW linpack_best10;
DROP
benchmark=> SELECT viewname FROM pg_views WHERE viewname = 'linpack_best10';
 viewname 
----------
(0 rows)