38.4. 在 INSERT, UPDATE, 和 DELETE上的規則 · PostgreSQL 中文文檔 9.3

# 38.4\. 在 `INSERT`, `UPDATE`, 和 `DELETE`上的規則定義在`INSERT`, `UPDATE`, `DELETE` 上的規則與前一章描述的視圖規則完全不同。首先，他們的`CREATE RULE`命令允許更多： * 它們可以沒有動作。 * 它們可以有多個動作。 * 他們可以是`INSTEAD`或`ALSO`(缺省)。 * 偽關系`NEW`和`OLD`變得有用了。 * 它們可以有規則資格條件。第二，它們不是就地修改查詢樹，而是創建零個或多個新查詢樹并且可能把原始的那個扔掉。 ## 38.4.1\. 更新規則是如何運轉的把下面語法： ``` CREATE [ OR REPLACE ] RULE _name_ AS ON _event_ TO _table_ [ WHERE _condition_ ] DO [ ALSO | INSTEAD ] { NOTHING | _command_ | ( _command_ ; _command_ ... ) } ``` 牢牢記住。在隨后的內容里，_update rules_(更新規則)意思是定義在 `INSERT`, `UPDATE`, 或 `DELETE`上的規則。如果查詢樹的結果關系和命令類型與`CREATE RULE`命令里給出的對象和事件一樣的話，規則系統就把更新規則應用上去。對于更新規則，規則系統創建一個查詢樹列表。一開始查詢樹是空的，這里可以有零個(`NOTHING`關鍵字)、一個、或多個動作。為簡單起見，先看一個只有一個動作的規則。這個規則可以有零個或一個條件并且它可以是 `INSTEAD`或`ALSO`(缺省)。何為規則條件?它是一個限制條件，告訴規則動作什么時候要做，什么時候不要做。這個條件可以只引用`NEW`和/或`OLD`偽關系，它們基本上是代表以對象形式給出的基本關系(但是有著特殊含義)。所以，對這個單動作的規則生成查詢樹，有下面三種情況。沒有條件，也沒有`ALSO`或`INSTEAD` 來自規則動作的查詢樹，附加了原始查詢樹的條件。給出了條件，有`ALSO` 來自規則動作的帶有規則條件的查詢樹并且附加了原始查詢樹的條件。給出了條件，有`INSTEAD` 來自規則動作帶有規則條件的查詢樹以及原始查詢樹的條件；以及附加了相反規則條件的原始查詢樹。最后，如果規則是`ALSO`，那么最初未修改的查詢樹被加入到列表。因為只有合格的`INSTEAD`規則已經在初始的查詢樹里面，所以對于單動作規則最終得到一個或者兩個查詢樹。對于`ON INSERT`規則，原來的查詢(如果沒有被`INSTEAD`取代) 是在任何規則增加的動作之前完成的。這樣就允許動作看到插入的行。但是對`ON UPDATE` 和`ON DELETE`規則，原來的查詢是在規則增加的動作之后完成的。這樣就確保動作可以看到將要更新或者將要刪除的行；否則，動作可能什么也不做，因為它們發現沒有符合它們要求的行。從規則動作生成的查詢樹被再次送到重寫系統，并且可能附加更多的規則，結果是更多的或更少的查詢樹。所以規則動作必須是另一個命令類型或者和規則所在的關系不同的另一個結果關系。否則這樣的遞歸過程就會沒完沒了(規則的遞規展開會被檢測到，并當作一個錯誤報告)。在`pg_rewrite`系統表中 action 里的查詢樹只是模板。因為他們可以引用范圍表的`NEW`和`OLD`，在使用它們之前必須做一些調整。對于任何對`NEW`的引用，都要先在初始查詢的目標列中搜索對應的條目。如果找到，把該條目表達式放到引用里。否則`NEW`和`OLD`的含義一樣(對于`UPDATE`) 或者被 NULL 替代(對于`INSERT`)。任何對`OLD` 的引用都用結果關系的范圍表的引用替換。在系統完成更新規則的附加之后，它再附加視圖規則到生成的查詢樹上。視圖無法插入新的更新動作，所以沒有必要向視圖重寫的輸出附加更新規則。 ### 38.4.1.1\. 循序漸進的第一個規則假設希望跟蹤`shoelace_data`關系中的`sl_avail`字段。所以設置一個日志表和一條規則，這條規則每次在用`UPDATE`更新 `shoelace_data`表時都要往數據庫里寫一條記錄。 ``` CREATE TABLE shoelace_log ( sl_name text, -- 鞋帶變化了 sl_avail integer, -- 新的可用數值 log_who text, -- 誰干的 log_when timestamp -- 什么時候 ); 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'; ``` 這里是一個帶有條件表達式的`ON UPDATE`規則`log_shoelace`： ``` NEW.sl_avail <> OLD.sl_avail ``` 和動作： ``` INSERT INTO shoelace_log VALUES ( new.sl_name, new.sl_avail, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old; ``` 這個輸出看起來有點奇怪，因為你不能寫`INSERT ... VALUES ... FROM`。這里的`FROM`子句只是表示查詢樹里有用于`new`和`old` 的范圍表記錄。這些東西的存在是因為這樣一來它們就可以被`INSERT` 命令的查詢樹里的變量引用。該規則是一個有條件的`ALSO`規則，所以規則系統必須返回兩個查詢樹：更改過的規則動作和原始查詢樹。在第一步里，原始查詢的范圍表集成到規則動作查詢樹里。生成： ``` 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**; ``` 第二步把規則條件增加進去，所以結果集限制為`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**; ``` 這個東西看起來更奇怪，因為`INSERT ... VALUES`也沒有`WHERE`子句，不過規劃器和執行器對此并不在意。它們畢竟還要為`INSERT ... SELECT`支持這種功能。第三步把原始查詢樹的條件加進去，把結果集進一步限制成只有被初始查詢樹改變的行： ``` 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'**; ``` 第四步把`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'; ``` 第五步用結果關系引用把`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`還輸出原始查詢樹。簡而言之，從規則系統輸出的是一個兩個查詢樹的列表，與下面語句相同： ``` 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'; ``` 這就是執行的順序以及規則要做的事情。做的替換和追加的條件用于確保如果原始的查詢是下面這樣： ``` UPDATE shoelace_data SET sl_color = 'green' WHERE sl_name = 'sl7'; ``` 就不會有日期記錄寫到表里。因為這回原始查詢樹不包含有關`sl_avail` 的目標列表，`NEW.sl_avail`將被`shoelace_data.sl_avail`代替，所以，規則生成的額外命令是： ``` INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, **shoelace_data.sl_avail**, current_user, current_timestamp ) FROM shoelace_data WHERE **shoelace_data.sl_avail** <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; ``` 并且條件將永遠不可能是真值。如果最初的查詢修改多個行，它也能運行。所以如果寫出下面命令： ``` UPDATE shoelace_data SET sl_avail = 0 WHERE sl_color = 'black'; ``` 實際上有四行被更新(`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'**; ``` 這個查詢樹將肯定插入三個新的日志記錄。這也是完全正確的。到這里就明白為什么原始查詢樹最后執行非常重要。如果`UPDATE`將先被執行，所有的行都已經設為零，所以記日志的`INSERT`將不能找到任何符合 `0 <> shoelace_data.sl_avail`條件的行。 ## 38.4.2\. 與視圖合作一個保護視圖關系，使其避免有人可以在其中`INSERT`, `UPDATE`, `DELETE`的簡單方法是讓那些查詢樹被丟棄。創建下面規則： ``` 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`做上面的任何操作，規則系統將應用這些規則。因為這些規則沒有動作而且是`INSTEAD`，結果是生成的查詢樹將是空的并且整個查詢將變得空空如也，因為經過規則系統處理后沒有什么東西剩下來用于優化或執行了。一個更復雜的使用規則系統的方法是用規則系統創建一個重寫查詢樹的規則，使查詢樹對真實的表進行正確的操作。要在視圖`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` 計算視圖行數的子句。這對于基于單個表的視圖來說通常非常瑣碎，但是連接諸如 `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 ) RETURNING shoelace_data.*, (SELECT shoelace_data.sl_len * u.un_fact FROM unit u WHERE shoelace_data.sl_unit = u.un_name); ``` 注意，這個規則同時支持該視圖上的`INSERT`和`INSERT RETURNING`查詢， `INSERT`將簡單的忽略`RETURNING`子句。假設現在有一包鞋帶到達商店，還有一個大的部件列表。但是不想每次都手工更新 `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) ``` 把到貨鞋帶移到(shoelace_ok)中： ``` 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`語句到這個結果經過了長長的一段過程。而且對查詢樹轉化的描述將是本文檔的最后。首先是生成分析器輸出： ``` INSERT INTO shoelace_ok SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok; ``` 現在應用第一條規則`shoelace_ok_ins`把它轉換成： ``` UPDATE shoelace SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace WHERE shoelace.sl_name = shoelace_arrive.arr_name; ``` 并且把原始的對`shoelace_ok`的`INSERT`丟棄掉。這樣重寫后的查詢再次傳入規則系統并且第二次應用了規則`shoelace_upd`生成： ``` UPDATE shoelace_data SET sl_name = shoelace.sl_name, sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant, sl_color = shoelace.sl_color, sl_len = shoelace.sl_len, sl_unit = shoelace.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data WHERE shoelace.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = shoelace.sl_name; ``` 同樣這是一個`INSTEAD`規則并且前一個查詢樹被丟棄掉。注意這個查詢仍然是使用視圖`shoelace`，但是規則系統還沒有完成這一步，所以它繼續在這上面應用規則`_RETURN`，然后得到： ``` UPDATE shoelace_data SET sl_name = s.sl_name, sl_avail = s.sl_avail + shoelace_arrive.arr_quant, sl_color = s.sl_color, sl_len = s.sl_len, sl_unit = s.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data, shoelace old, shoelace new, shoelace_data s, unit u WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name; ``` 最后，應用規則`log_shoelace`，生成額外的查詢樹： ``` INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data, shoelace old, shoelace new, shoelace_data s, unit u, shoelace_data old, shoelace_data new shoelace_log shoelace_log WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail; ``` 這樣，在規則系統用完所有的規則后返回生成的查詢樹。所以最終得到兩個等效于下面SQL語句的查詢樹： ``` INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail; UPDATE shoelace_data SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.sl_name AND shoelace_data.sl_name = s.sl_name; ``` 結果是從一個關系來的數據插入到另一個中，到了第三個中變成更新，在到第四個中變成更新加上記日志，最后在第五個規則中縮減為兩個查詢。有一個小細節有點讓人難受。看看生成的兩個查詢，會發現`shoelace_data` 關系在范圍表中出現了兩次而實際上絕對可以縮為一次。因為規劃器不處理這些，所以對規則系統輸出的`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 ``` 這也會在日志關系中生成完全一樣的記錄。因此，規則系統導致對表`shoelace_data` 的一次多余的掃描，而且同樣多余的掃描會在`UPDATE`里也一樣多做一次。不過要想把這些不足去掉是一樣太困難的活了。最后對PostgreSQL規則系統及其功能做一個演示。假設你向你的數據庫中添加一些比較罕見的鞋帶： ``` INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0); INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); ``` 建立一個視圖檢查哪種`shoelace`記錄在顏色上和任何鞋子都不相配。用于這個的視圖是： ``` 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 ``` 現在想這樣設置：沒有庫存的不匹配的鞋帶都從數據庫中刪除。為了讓這事對 PostgreSQL有點難度，不直接刪除它們。取而代之的是再創建一個視圖: ``` 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) ``` 對一個視圖的`DELETE`，這個視圖帶有一個總共使用了四個嵌套/連接的視圖的子查詢條件，這四個視圖之一本身有一個擁有對一個視圖的子查詢條件，該條件計算使用的視圖的列；最后重寫成了一個查詢樹，該查詢樹從一個真正的表里面把需要刪除的數據刪除。我想在現實世界里只有很少的機會需要上面的這樣的構造。但這些東西能運轉肯定讓你舒服。