| PostgreSQL 7.4 文檔 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 34. 規則系統 | Fast Forward | Next |
定義在 INSERT，UPDATE 和 DELETE 的規則與前一章描述的視圖規則完全不同。 首先，他們的CREATE RULE命令允許更多：
允許它們可以沒有動作（action）。
它們可以有多個動作（action）。
關鍵字 INSTEAD 是可選的。
偽關系 NEW 和 OLD 變得有用了。
它們可以有規則資格條件。
第二，它們不是就地修改查詢樹。 它們是創建零個或多個新查詢樹並且可能把原始的那個仍掉。
把下面語法
CREATE RULE rule_name AS ON event
TO object [WHERE rule_qualification]
DO [INSTEAD] [action | (actions) | NOTHING];牢牢記住。在隨後的內容裡， "update rules"(更新規則) 意思是定義在 INSERT，UPDATE 或 DELETE 上的規則。
如果查詢樹的結果關系和命令類型與CREATE RULE 命令裡給出的對象和事件一樣的話， 規則系統就把更新規則應用上去。 對于更新規則，規則系統創建一個查詢樹列表。 一開始查詢樹是空的。這裡可以有零個（NOTHING 關鍵字），一個或多個動作。 一個或多個動作。為簡單起見， 我們先看一眼一個只有一個動作（action）的規則。 這個規則可以有一個條件或沒有並且它可以是 INSTEAD 或反之。
何為規則條件？它是一個限制條件，告訴規則動作（action）什麼時候要做， 什麼時候不用做。這個資格（條件）可以只引用 NEW 和/或 OLD 偽關系， 它們基本上是代表以對象形式給出的基本關系（但是有著特殊含義）。
所以，對這個單動作（action）的規則生成查詢樹，有下面四種情況。
來自規則動作的查詢樹，附加了原始查詢樹的條件。
來自規則動作的查詢樹，附加了原始查詢樹的條件。
來自規則動作的帶有規則條件的查詢樹並且附加了原始查詢樹的條件。
來自規則動作帶有規則條件的查詢樹以及原始查詢樹的條件； 以及附加了相反規則條件的原始查詢樹。
最後，如果規則不是 INSTEAD，最初的未修改的查詢樹被加入到列表。 因為只有合格的INSTEAD規則已經在初始的查詢樹裡面， 所以對于單動作規則我們最終得到一個或者兩個查詢樹。
對于 ON INSERT 規則， 原來的查詢（如果沒有被 INSTEAD 取代）是在任何規則增加的動作之前完成的。 這樣就允許動作看到插入的行。但是對于 ON UPDATE 和 ON DELETE 規則，原來的查詢是在規則增加的動作之後完成的。 這樣就確保動作可以看到將要更新或者將要刪除的行；否則，動作可能什麼也不做，因為它們發現沒有符合它們的要求的行。
從規則動作生成的查詢樹被再次送到重寫系統，並且可能附加更多的規則， 結果是更多的或更少的查詢樹。所以規則動作裡的查詢樹必須是另一個命令類型或另一個結果關系。 否則這樣的遞歸過程就會沒完沒了。 現在有一個 100 層的固定的遞規迭代限制。 如果 100 次迭代之後還有需要應用的更新規則（update rules）， 規則系統就認為是一個在多規則定義上的循環因而報告一個錯誤。
在 pg_rewrite 系統表裡的 action 裡的查詢樹只是模板。 因為他們可以引用範圍表的 NEW 和 OLD， 在使用它們之前必須做一些調整。對于任何對 NEW 的引用， 都要先在初始查詢的目標列中搜索對應的條目。如果找到， 把該條目表達式放到引用裡。否則 NEW 和 OLD 的含義一樣 （UPDATE時）或者被 NULL 替代（INSERT時）。 任何對 OLD 的引用都用結果關系的範圍表的引用替換。
在系統完成更新規則的附加之後，它再附加視圖規則到生成的查詢樹上。 視圖無法插入新的更新動作，所以沒有必要向視圖重寫的輸出附加更新規則。
假設我們希望跟蹤 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命令的查詢樹裡的變量引用。）
該規則是一個有條件的非 INSTEAD 規則， 所以規則系統必須返回兩個查詢樹： 更改過的規則動作和原始查詢樹。 在第一步裡，原始查詢的範圍表集成到規則動作查詢樹裡。生成：
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';
第四步把 NEWNEW 引用替換為從原始查詢樹的目標列來的或從結果關系來的相匹配的變量引用：
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';
這就成了。因為規則不是 INSTEAD，我們還輸出原始查詢樹。 簡而言之，從規則系統輸出的是一個兩個查詢樹的列表，與下面語句相同：
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 條件的。
一個保護視圖關系， 使其避免我們曾提到的有人可以在其中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;
現在有一包鞋帶到達商店，而且這是一大筆到貨。 但是我們不想每次都手工更新 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， 這個視圖帶有一個總共使用了四個嵌套/連接的視圖的子查詢條件， 這四個視圖之一本身有一個擁有對一個視圖的子查詢條件， 該條件計算使用的視圖的列；最後重寫成了一個查詢樹， 該查詢樹從一個真正的表裡面把需要刪除的數據刪除。
我想在現實世界裡只有很少的機會需要上面的這樣的構造。 但這些東西能運轉肯定讓你舒服。