INSERT、UPDATE、DELETE 文のルールの動作 - PolarDB - Alibaba Cloud - PolarDB

INSERT、UPDATE、DELETE で定義されたルールは、ビュールールとは異なる動作をします。これらは、元のクエリツリーを直接変更するのではなく、0 個以上の新しいクエリツリーを生成します。また、CREATE RULE 構文でより広範なオプションをサポートします。

ルールにアクションがない場合があります。
ルールに複数のアクションがある場合があります。
ルールは INSTEAD または ALSO (デフォルト) にすることができます。
NEW および OLD 擬似リレーションは、ルールのアクションと条件において意味を持ちます。
ルールには条件 (条件式) を含めることができます。

警告

多くの場合、INSERT/UPDATE/DELETE のルールで処理できるタスクは、トリガーを使用する方が適しています。トリガーは表記がより複雑ですが、セマンティクスは理解しやすいです。元のクエリに揮発性関数が含まれている場合、ルールは予期しない結果を生むことがあります。ルールが適用される際に、揮発性関数が想定よりも多く実行される可能性があるためです。

更新ルールでは、以下の構文はサポートされていません。

元のクエリの WITH 句
UPDATE クエリの SET リストにおける、複数代入のサブ SELECT

これらの構文をルールクエリにコピーすると、サブクエリが複数回評価されることになり、元のクエリの意図に反します。

更新ルールの仕組み

CREATE RULE の構文は次のとおりです。

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

この文脈では、*更新ルール* とは INSERT、UPDATE、または DELETE で定義されたルールを指します。

ルールシステムは、クエリツリーの結果リレーションとコマンドタイプが CREATE RULE コマンドのオブジェクトとイベントに一致する場合に更新ルールを適用します。その後、ルールシステムは空のクエリツリーのリストを構築します。ルールには、アクションがない (NOTHING) 場合、1 つのアクションがある場合、または複数のアクションがある場合があります。

*ルール条件* とは、ルールのアクションがいつ実行されるかを制御する条件です。これは、変更前後のターゲットリレーションを表す NEW および OLD 擬似リレーションのみを参照できます。

1 つのアクションを持つルールの場合、ルールシステムは次の 3 つのケースに従ってクエリツリーを生成します。

ケース	出力クエリツリー
条件なし、`ALSO` または `INSTEAD`	元のクエリの条件が追加されたルールアクションのクエリツリー
条件あり、`ALSO`	ルール条件と元のクエリの条件の両方が追加されたルールアクションのクエリツリー
条件あり、`INSTEAD`	両方の条件が追加されたルールアクションのクエリツリーに加えて、否定されたルール条件が追加された元のクエリツリー

ALSO ルールの場合、変更されていない元のクエリツリーが出力リストに追加されます。条件付きの INSTEAD ルールはすでに変更された形式の元のクエリツリーを追加するため、1 つのアクションを持つルールの最終的な出力は常に 1 つまたは 2 つのクエリツリーになります。

実行順序：

ON INSERT ルール：元のクエリ (INSTEAD によって抑制されていない場合) は、ルールによって追加されたアクションの*前*に実行されます。これにより、ルールのアクションは挿入された行を参照できます。
ON UPDATE および ON DELETE ルール：元のクエリは、ルールによって追加されたアクションの*後*に実行されます。これにより、アクションは更新または削除される前の行を参照できます。

生成されたクエリツリーは書き換えシステムにフィードバックされ、そこで追加のルールが適用される場合があります。無限ループを防ぐために、ルールのアクションは、コマンドタイプまたは結果リレーションのいずれかでルール自体と異なる必要があります。再帰的展開は検出され、エラーとして報告されます。

pg_rewrite システムカタログに格納されているクエリツリーはテンプレートです。使用する前に、システムは NEW および OLD への参照を置換します。

NEW 参照は、元のクエリのターゲットリスト内の対応するエントリに置き換えられます。一致が見つからない場合、NEW は OLD (UPDATE の場合) または NULL 値 (INSERT の場合) に解決されます。
OLD 参照は、結果リレーションの範囲テーブルエントリへの参照に置き換えられます。

更新ルールが適用された後、システムは結果のクエリツリーにビュールールを適用します。ビューは新しい更新アクションを導入できないため、更新ルールがビューの書き換えの出力に適用されることはありません。

ステップバイステップ：クエリ書き換えによるルールのトレース

内部で何が起こるかをトレースするために、shoelace_data テーブルの sl_avail 列への変更をログに記録するルールを考えてみましょう。まず、ログテーブルとルールを作成します。

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who made the change
    log_when   timestamp      -- when the change occurred
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

更新を実行します。

UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

ログを確認します。

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)

各ステージで何が起こるかを以下に示します。パーサは以下を生成します。

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

ルール log_shoelace は条件付きの ALSO ルールであるため、システムは 2 つのクエリツリーを返します。1 つは変更されたルールアクション、もう 1 つは元のクエリツリーです。

ステップ 1 — 元のクエリの範囲テーブルをルールアクションに組み込みます。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data;

ステップ 2 — ルール条件を追加して、出力を sl_avail が変更された行に制限します。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE new.sl_avail <> old.sl_avail;

ステップ 3 — 元のクエリの条件を追加して、出力を元のクエリが影響を与えたであろう行に制限します。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE new.sl_avail <> old.sl_avail
   AND shoelace_data.sl_name = 'sl7';

ステップ 4 — NEW 参照を、ターゲットリストのエントリまたは結果リレーションからの一致する列参照に置き換えます。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 6 <> old.sl_avail
   AND shoelace_data.sl_name = 'sl7';

ステップ 5 — OLD 参照を結果リレーションの参照に置き換えます。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

ルールは ALSO であるため、システムは元のクエリツリーも出力します。最終的な出力は、以下に相当する 2 つのクエリツリーです。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';

これらはこの順序で実行され、これはまさにルールが意図した動作です。

置換と追加された条件は、エッジケースも正しく処理します。元のクエリが sl_avail の代わりに sl_color を更新した場合：

UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';

ログエントリは書き込まれません。元のクエリのターゲットリストに sl_avail のエントリがないため、NEW.sl_avail は shoelace_data.sl_avail に置き換えられます。これにより、ルールアクションの WHERE 句は shoelace_data.sl_avail <> shoelace_data.sl_avail と評価され、これは決して true になりません。

このルールは一括更新も正しく処理します。例えば：

UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';

これは 4 つの行 (sl1、sl2、sl3、sl4) に影響しますが、sl3 はすでに sl_avail = 0 です。ルールは追加のクエリツリーを生成します。

INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
       current_user, current_timestamp
  FROM shoelace_data
 WHERE 0 <> shoelace_data.sl_avail
   AND shoelace_data.sl_color = 'black';

これにより、正確に 3 つのログエントリが挿入されます。これは正しい動作です。なぜなら sl3 には変更がなかったからです。ここでは実行順序が重要です。もし UPDATE が先に実行されていたら、すべての行はすでにゼロに設定されており、ロギングの INSERT は 0 <> shoelace_data.sl_avail に一致する行を見つけられなかったでしょう。

ビューとの連携

ビューの変更からの保護

ビューに対する INSERT、UPDATE、または DELETE を防ぐには、受信したクエリツリーを破棄する INSTEAD NOTHING ルールを作成します。

CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
    DO INSTEAD NOTHING;

shoe ビューを変更しようとすると、空のクエリツリーリストが生成され、何も最適化も実行もされません。

更新可能なビューの作成

より柔軟なアプローチは、ビューに対するクエリを、基になるテーブルへの操作に書き換えることです。shoelace ビューの場合：

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    );

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

CREATE RULE shoelace_del AS ON DELETE TO shoelace
    DO INSTEAD
    DELETE FROM shoelace_data
     WHERE sl_name = OLD.sl_name;

ビューでの RETURNING クエリのサポート

ビューで RETURNING をサポートするには、ルールに RETURNING 句を含めます。単一テーブルのビューの場合、これは簡単です。

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    )
    RETURNING
           shoelace_data.*,
           (SELECT shoelace_data.sl_len * u.un_fact
            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);

1 つのルールで、ビューに対する INSERT と INSERT RETURNING の両方のクエリをサポートします。通常の INSERT の場合、RETURNING 句は無視されます。

結合ビューの場合、RETURNING 句の構築はより複雑になります。

複数テーブルにまたがるルールの連鎖

ルールは、複数のテーブルやビューにまたがって連鎖させることができます。次の例では、2 つの補助テーブルを使用して、出荷が到着するたびに shoelace ビューを手動で更新することなく、バッチ在庫更新を処理します。

CREATE TABLE shoelace_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE shoelace_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
    DO INSTEAD
    UPDATE shoelace
       SET sl_avail = sl_avail + NEW.ok_quant
     WHERE sl_name = NEW.ok_name;

到着した在庫を shoelace_arrive にロードします。

SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)

現在の靴ひもの在庫状況を確認します。

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      |        6 | 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)

到着した在庫を移動します。

INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;

更新された在庫とログを確認します。

SELECT * FROM shoelace ORDER BY sl_name;

 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      |        6 | brown    |     60 | cm      |        60
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl3      |       10 | black    |     35 | inch    |      88.9
 sl8      |       21 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | brown    |    0.9 | m       |        90
(8 rows)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when
---------+----------+--------+----------------------------------
 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)

単一の INSERT ... SELECT が 4 段階のルールチェーンをトリガーしました。クエリツリーの変換は次のように進みます。

パーサが INSERT INTO shoelace_ok のクエリツリーを生成します。
ルール shoelace_ok_ins がそれを shoelace ビューに対する UPDATE に書き換え、元の INSERT を破棄します。
ルール shoelace_upd が shoelace に対する UPDATE を shoelace_data に対する UPDATE に書き換え、前のクエリツリーを破棄します。
_RETURN ルールが適用され、ビュー参照が展開されます。
ルール log_shoelace が追加の INSERT INTO shoelace_log クエリツリーを生成します。

最終的な出力は、プランナーとエグゼキュータが処理する 2 つのクエリツリーです。これらは INSERT INTO shoelace_log と UPDATE shoelace_data に相当します。

あるリレーションのデータが 2 番目のリレーションに挿入され、3 番目、そして 4 番目のリレーションへの更新に変換され、5 番目のリレーションにログ記録されるという一連の処理が、2 つのクエリに集約されます。

パフォーマンスに関する注意： ルールシステムは、範囲テーブルに重複したエントリを導入する可能性があります。プランナーはこれらの余分なエントリを削除しないため、追加のシーケンシャルスキャンが発生する可能性があります。この例では、shoelace_data が範囲テーブルに 2 回出現し、1 回の不要な追加スキャンが発生します。同じ冗長なスキャンは UPDATE でも発生します。これは、ルールシステムの書き換えアプローチにおける既知のトレードオフです。

INSERT ステップの実行計画は次のとおりです。

Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data

余分な範囲テーブルエントリがなければ、計画は次のようになります。

Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on shoelace_arrive

複数のビューレイヤーを介した DELETE の書き換え

ルールシステムの書き換え機能の深さを示すために、珍しい色の靴ひもをいくつか追加します。

INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);

どの靴の色とも一致しない靴ひもを特定するためのビューを作成します。

CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);

その出力：

SELECT * FROM shoelace_mismatch;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl9     |        0 | pink     |     35 | inch    |      88.9
 sl10    |     1000 | magenta  |     40 | inch    |     101.6

在庫切れの不一致を特定するための 2 番目のビューを作成します。

CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;

このビューチェーンを介して、在庫切れの不一致を削除します。

DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);

結果を確認します。

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     |        6 | brown    |     60 | cm      |        60
 sl4     |        8 | black    |     40 | inch    |     101.6
 sl3     |       10 | black    |     35 | inch    |      88.9
 sl8     |       21 | brown    |     40 | inch    |     101.6
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
 sl5     |        4 | brown    |      1 | m       |       100
 sl6     |       20 | brown    |    0.9 | m       |        90
(9 rows)

sl9 (ピンク、在庫ゼロ) は削除されます。sl10 (マゼンタ、在庫 1000) は、在庫ゼロの条件を満たさないため保持されます。

ビューに対する DELETE は、4 つのネストまたは結合されたビューにまたがるサブクエリによって修飾されています。そのうちの 1 つは、別のビューを含む独自のサブクエリ条件を持ち、計算ビュー列を使用しています。この DELETE は、基になるテーブルからターゲット行を削除する単一のクエリツリーに書き換えられます。