無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

38.2. ビューとルールシステム

PostgreSQL におけるビューはルールシステムを使って実装されています。 実際、

CREATE VIEW myview AS SELECT * FROM mytab;

CREATE TABLE myview (

same column list as mytab

);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

の2つのコマンドの間には基本的な違いはありません。 と言うのは、 CREATE VIEW コマンドによって内部的にまったく同じコマンドが行われるからです。 これには副作用もあります。 その1つは PostgreSQL システムカタログのビューについての情報はテーブルの情報とまったく同一であるということです。 そのため、パーサにとってはテーブルとビューの間に違いは一切ありません。 これらは同じもの、つまりリレーションです。

38.2.1. SELECT ルールの動き

たとえコマンドが INSERT UPDATE DELETE などであっても、 ON SELECT ルールは全ての問い合わせに対し最後に適用されます。 そして、このルールは他のコマンド種類のルールと異なるセマンティックを持っていて、問い合わせツリーを新規に生成せずに、そこにあるものを修正します。 したがって SELECT ルールが一番初めに記述されなければいけません。

現在のところ、 ON SELECT ルールでは1つのアクションしか許されず、それは INSTEAD である無条件の SELECT アクションでなければいけません。 この制約は、一般のユーザが何をしても、ルールシステムが堅牢であるために必要であり、 ON SELECT のルールはビュー同様の動作に限定されます。

本章の例として挙げているのは、ちょっとした演算をする2つの結合のビューと、次にこれらの機能を利用するいくつかのビューです。 最終結果が何らかの魔法の機能によりあたかも実テーブルのように振舞うビューになるように、初めの2つのビューのうちの1つは、 INSERT UPDATE DELETE 操作に対するルールを後で追加することでカスタマイズされます。 初めて学ぶための例としては決して簡単ではなく先に進むことを躊躇させるかもしれませんが、多くの別々の例を持ち出して頭の混乱を招くよりも、全ての論点をステップごとに追う1つの例を挙げる方が良いでしょう。

この例では、2つの整数から小さな値を返すちょっとした min 関数を必要とします。 関数の生成は以下のようにします。

CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
    SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;

最初の2つのルールシステムの説明で必要とする実テーブルを以下に示します。

CREATE TABLE shoe_data (
    shoename   text,          -- プライマリキー
    sh_avail   integer,       -- 在庫
    slcolor    text,          -- 望ましい靴紐の色
    slminlen   real,          -- 靴紐の最短サイズ
    slmaxlen   real,          -- 靴紐の最長サイズ
    slunit     text           -- 長さの単位
);

CREATE TABLE shoelace_data (
    sl_name    text,          -- プライマリキー
    sl_avail   integer,       -- 在庫
    sl_color   text,          -- 靴紐の色
    sl_len     real,          -- 靴紐の長さ
    sl_unit    text           -- 長さの単位
);

CREATE TABLE unit (
    un_name    text,          -- プライマリキー
    un_fact    real           -- cmに変換するファクタ
);

これでわかるかもしれませんが、これらは靴屋のデータを表しています。

ビューを以下のように作成します。

CREATE VIEW shoe AS
    SELECT sh.shoename,
           sh.sh_avail,
           sh.slcolor,
           sh.slminlen,
           sh.slminlen * un.un_fact AS slminlen_cm,
           sh.slmaxlen,
           sh.slmaxlen * un.un_fact AS slmaxlen_cm,
           sh.slunit
      FROM shoe_data sh, unit un
     WHERE sh.slunit = un.un_name;

CREATE VIEW shoelace AS
    SELECT s.sl_name,
           s.sl_avail,
           s.sl_color,
           s.sl_len,
           s.sl_unit,
           s.sl_len * u.un_fact AS sl_len_cm
      FROM shoelace_data s, unit u
     WHERE s.sl_unit = u.un_name;

CREATE VIEW shoe_ready AS
    SELECT rsh.shoename,
           rsh.sh_avail,
           rsl.sl_name,
           rsl.sl_avail,
           min(rsh.sh_avail, rsl.sl_avail) AS total_avail
      FROM shoe rsh, shoelace rsl
     WHERE rsl.sl_color = rsh.slcolor
       AND rsl.sl_len_cm >= rsh.slminlen_cm
       AND rsl.sl_len_cm <= rsh.slmaxlen_cm;

shoelace ビュー(今ある一番簡単なビュー)用の CREATE VIEW コマンドは、 shoelace リレーションと、問い合わせ範囲テーブルの中で shoelace リレーションが参照される時はいつでも、適用されるべき書き換えルールの存在を示す項目を pg_rewrite に作ります。 ルールはルール条件( SELECT ルールは現在持つことができませんので、非 SELECT ルールのところで取り上げます)を持たない INSTEAD です。 ルール条件は問い合わせ条件とは異なることに注意してください! ルールアクションは問い合わせ条件を持っています。 このルールアクションは、ビュー作成コマンド内の SELECT のコピーである、1つの問い合わせツリーです。

注意: pg_rewrite 項目の NEW OLD に対する2つの特別な範囲テーブル項目は SELECT ルールには関係ありません。

ではここで unit shoe_data shoelace_data にデータを入れ、ビューに簡単な問い合わせを行います。

INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);

INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');

INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');

SELECT * FROM shoelace;

 sl_name   | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
 sl1       |        5 | black    |     80 | cm      |        80
 sl2       |        6 | black    |    100 | cm      |       100
 sl7       |        7 | brown    |     60 | cm      |        60
 sl3       |        0 | black    |     35 | inch    |      88.9
 sl4       |        8 | black    |     40 | inch    |     101.6
 sl8       |        1 | brown    |     40 | inch    |     101.6
 sl5       |        4 | brown    |      1 | m       |       100
 sl6       |        0 | brown    |    0.9 | m       |        90
(8 rows)

これは、ビューに対する最も簡単な SELECT ですので、この機会にビュールールの基本を説明します。 SELECT * FROM shoelace はパーサによって処理され、次の問い合わせツリーが生成されます。

SELECT shoelace.sl_name, shoelace.sl_avail,
       shoelace.sl_color, shoelace.sl_len,
       shoelace.sl_unit, shoelace.sl_len_cm
  FROM shoelace shoelace;

このツリーがルールシステムに伝えられます。 ルールシステムは範囲テーブルを参照し、何らかのリレーションに対してルールが存在するか調べます。 shoelace (現時点では唯一のビュー)についての範囲テーブル項目を処理する際、問い合わせツリーで _RETURN ルールを検出します。

SELECT s.sl_name, s.sl_avail,
       s.sl_color, s.sl_len, s.sl_unit,
       s.sl_len * u.un_fact AS sl_len_cm
  FROM shoelace old, shoelace new,
       shoelace_data s, unit u
 WHERE s.sl_unit = u.un_name;

ビューを展開するために、リライタは単純にルールのアクション問い合わせツリーを持つ副問い合わせ範囲テーブルの項目を作り、ビューを参照していた元の範囲テーブルを置き換えます。 書き換えられた結果の問い合わせツリーは、以下のように入力した場合とほぼ同じです。

SELECT shoelace.sl_name, shoelace.sl_avail,
       shoelace.sl_color, shoelace.sl_len,
       shoelace.sl_unit, shoelace.sl_len_cm
  FROM (SELECT s.sl_name,
               s.sl_avail,
               s.sl_color,
               s.sl_len,
               s.sl_unit,
               s.sl_len * u.un_fact AS sl_len_cm
          FROM shoelace_data s, unit u
         WHERE s.sl_unit = u.un_name) shoelace;

しかし1つだけ違いがあります。 副問い合わせの範囲テーブルが2つの余分な項目 shoelace old shoelace new を持っていることです。 これらの項目は副問い合わせの結合ツリーや目的リストで参照されませんので、直接問い合わせでは使われません。 リライタはそれらを使用して、ビューを参照した範囲テーブルの項目に元々存在したアクセス権限確認情報を格納します。 この方法で、書き換えられた問い合わせで直接ビューを使用していなくても、エクゼキュータはユーザがそのビューにアクセスするための正しい権限を持っているか確認します。

これが最初に適用されるルールです。 ルールシステムは最上位の問い合わせの残り(この例ではこれ以上ありません)の範囲テーブルの項目をチェックし続けます。 そしてルールシステムは、追加された副問い合わせの範囲テーブルの項目がビューを参照するかを再帰的に確認します (しかし old new は展開しません。 そうでなければ無限再帰になってしまいます!)。 この例では shoelace_data unit 用の書き換えルールはありません。 ですから書き換えは完結し、上記がプランナに渡される最終的な結果となります。

さて、店に置いてある靴紐(の色とサイズ)に一致する靴が店にあるか、完全に一致する靴紐の在庫数が2以上あるかどうかを把握する問い合わせを書いてみましょう。

SELECT * FROM shoe_ready WHERE total_avail >= 2;

 shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
 sh1      |        2 | sl1     |        5 |           2
 sh3      |        4 | sl7     |        7 |           4
(2 rows)

今回のパーサの出力は以下の問い合わせツリーです。

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM shoe_ready shoe_ready
 WHERE shoe_ready.total_avail >= 2;

最初に適用されるルールは shoe_ready ビュー用のもので、問い合わせツリーにおける結果は以下のようになります。

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               min(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM shoe rsh, shoelace rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail >= 2;

同じように、 shoe shoelace 用のルールは副問い合わせの範囲テーブルとして代用され、3レベルの最終問い合わせツリーへと導きます。

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               min(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM (SELECT sh.shoename,
                       sh.sh_avail,
                       sh.slcolor,
                       sh.slminlen,
                       sh.slminlen * un.un_fact AS slminlen_cm,
                       sh.slmaxlen,
                       sh.slmaxlen * un.un_fact AS slmaxlen_cm,
                       sh.slunit
                  FROM shoe_data sh, unit un
                 WHERE sh.slunit = un.un_name) rsh,
               (SELECT s.sl_name,
                       s.sl_avail,
                       s.sl_color,
                       s.sl_len,
                       s.sl_unit,
                       s.sl_len * u.un_fact AS sl_len_cm
                  FROM shoelace_data s, unit u
                 WHERE s.sl_unit = u.un_name) rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail > 2;

次にプランナはこのツリーを2レベルの問い合わせツリーに縮めます。 一番下の SELECT コマンドは別々に処理する必要がありませんので2つ目の SELECT "引っ張り上げ" られます。 しかし2つ目の SELECT は集約関数を持つため、頂点からは区別されます。 もしそれらを引っ張り上げてしまうと一番上の SELECT の動作を変えてしまうことになり、それはしたくありません。 しかし、問い合わせツリーを縮めるという最適化を、書き換えシステム自身で意識する必要はありません。

38.2.2. 非 SELECT 文のビュールール

これまでのビュールールの説明では問い合わせツリーの2つの詳細について触れませんでした。 それらは、コマンドタイプと結果リレーションです。 実際、コマンドタイプはビュールールでは必要とされませんが、結果リレーションがビューの場合には特別な考慮が必要ですので、結果リレーションは問い合わせリライタの動作に影響するかもしれません。

SELECT と他のコマンドに対する問い合わせツリーの間には大きな違いはありません。 それらは明らかに違うコマンドタイプを持っていて、 SELECT 以外のコマンドでは、結果リレーションは結果の格納先となる範囲テーブルの項目を指し示します。 それ以外ではまったく同じです。 ですから、 a b の列を持つテーブル t1 t2 に対する以下の2つの文の問い合わせツリーは、ほとんど同じです。

SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;

以下に、具体的に示します。

  • 範囲テーブルには、テーブル t1 t2 に対する項目があります。

  • 目的リストにはテーブル t2 に対する範囲テーブル項目の b 列を指し示す1つの変数があります。

  • 条件式は、範囲テーブルの両項目の a 列の等価性を比較します。

  • 結合ツリーは t1 t2 の単純な結合を表しています。

結果として、両方の問い合わせツリーは似たような実行計画になります。 それらはともに2つのテーブルの結合です。 UPDATE では t1 から抜けている列はプランナによって目的リストに追加され、最終の問い合わせツリーは、以下のようになります。

UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;

そして、結合を実行したエクゼキュータは、

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;

の結果集合とまったく同じ結果集合を作成します。 とは言っても UPDATE にはちょっとした問題があります。 結合を行うエクゼキュータの計画の部分は、結合の結果が何に向けられているかに関与しません。 エクゼキュータは単に結果となる行の集合を作成するだけです。 1つは SELECT コマンドでもう1つは UPDATE コマンドであるという事実は、エクゼキュータの中のより上位で扱われます。 そこでは、これが UPDATE であるとわかっていて、この結果がテーブル t1 に入らなければいけないことを知っています。 しかし、そこにあるどの行が新しい行によって置換されなければならないのでしょうか。

この問題を解決するため、 UPDATE 文( DELETE 文の場合も同様)の目的リストに別の項目が付け加えられます。 それは、現在のタプルID( CTID )です。 これはその行のファイルブロック番号とブロック中の位置を持つシステム列です。 テーブルがわかっている場合、 CTID を使用して、元の t1 行を抽出して更新することができます。 CTID を目的リストに追加すると、問い合わせは以下のようになります。

SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;

では、 PostgreSQL の別の詳細説明に入りましょう。 テーブルの行は上書きされませんので、 ROLLBACK 処理は速いのです。 UPDATE では、( CTID を取り除いた後)テーブルに新しい結果行が挿入され、 CTID が指し示す古い行の行ヘッダ内の cmax xmax 項目は現在のコマンドカウンタと現在のトランザクションIDに設定されます。 このようにして、古い行は隠され、トランザクションがコミットされた後、vacuum掃除機が不必要になった行をそのうちに削除できます。

これらの詳細が全部理解できれば、どんなコマンドに対してもまったく同じようにしてビューのルールを簡単に適用することができます。 そこには差異がありません。

38.2.3. PostgreSQL におけるビューの能力

ここまでで、ルールシステムがどのようにビューの諸定義を元の問い合わせツリーに組み入れるかを解説しました。 第2の例では、1つのビューからの単純な SELECT によって、最終的に4つのテーブルを結合する問い合わせツリーが生成されました( unit は違った名前で2回使われました)。

ビューをルールシステムで実装する利点は、どのテーブルをスキャンすべきか、それらのテーブル間の関連性、ビューからの制約条件、元の問い合わせ条件に関する情報を全て、プランナが1つの問い合わせツリーの中に持っていることです。 元の問い合わせが既にビューに対する結合である時も同様です。 プランナはここでどれが問い合わせ処理の最適経路かを決定しなければなりません。 プランナは保持する情報が多ければ多いほど、より良い決定を下すことができます。 そして PostgreSQL に実装されているルールシステムはこれが現時点で、提供されている全ての情報であることを保証します。

38.2.4. ビューの更新について

ビューが INSERT UPDATE DELETE などの目的リレーションとして名付けられた場合はどうなるのでしょうか? 上で説明したような置換をすると、結果リレーションが副問い合わせの範囲テーブル項目を指す問い合わせツリーができてしまい、それは上手く機能しません。しかし、いくつかのケースでは PostgreSQL はビューの更新をサポートする事ができます。

副問い合わせが単一のテーブルを参照しかつ十分に単純である時、リライタは副問い合わせを被参照テーブルに自動的に置き換え、したがって、 INSERT UPDATE あるいは DELETE を適切な方法で被参照テーブルに適用する事ができます。 この場合の "十分に単純" であるとは 自動的に更新可能 ある事です。より詳細な自動的に更新可能なビューの情報については、 CREATE VIEW を参照してください。

もう一つの方法として、ビューに対するユーザ定義の INSTEAD OF トリガによってこれらのコマンドを処理する事ができます。この場合、書き換えは少々違う形で行われます。 INSERT に対しては、リライタはビューに全く何もせず、問い合わせの結果リレーションをそのままにします。 UPDATE DELETE に対しては、コマンドが更新もしくは削除しようとする "古い" 行を生成するためにビュー問い合わせを展開する必要がまだあります。 そのため、ビューは通常通り展開されますが、もう一つの展開されない範囲テーブル項目が結果リレーションとしてビューを表す問い合わせに追加されます。

ここで起こる問題はビューで更新される行をどのように特定するかということです。 結果リレーションがテーブルの場合、更新する行の物理的な位置を特定するために特別な CTID 項目が目的リストに追加されることを思い出して下さい。 ビューの行には実際の物理的な位置がないため、ビューには CTID がありませんので、これは結果リレーションがビューの場合には上手くいきません。 その代わり、 UPDATE DELETE 操作では、特別な 行全体 の項目が目的リストに追加されていて、それはビューからすべての列を含むように展開されています。 エクゼキュータこの値を使って "古い" 行を INSTEAD OF トリガに提供します。 新旧の行の値に基づいて更新するものを計算するのはトリガの責任です。

別の方法としては、ビューに対する INSERT UPDATE DELETE コマンドに代替の動作を指定する INSTEAD ルールを定義する事です。 これらのルールは、ビューではなくコマンドを、通常は1つもしくは複数のテーブルを更新するコマンドに書き換えます。 それが次節の論題になります。

ルールが最初に評価され、元の問い合わせが計画され実行される前にそれを書き換えることに注意して下さい。 そのためビューに INSTEAD OF トリガと INSERT UPDATE DELETE に関するルールがあった場合、ルールが最初に評価され、その結果よってはトリガが全く使われないかもしれません。

単純なビューに対する INSERT UPDATE あるいは DELETE コマンドの自動書き換えは常に最後に試みられます。したがって、ビューがルールもしくはトリガを持っていた場合、これらは更新可能ビューのデフォルト動作を上書きします。

ビューに INSTEAD ルールも INSTEAD OF トリガも定義されておらず、かつ、リライタがクエリを自動的に被参照テーブルへの更新に書き換える事ができなかった場合、エグゼキュータはビューを更新できませんのでエラーが発生します。


powered by SEO.CUG.NET