# 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; ``` 之間本質上沒有區別，因為這就是`CREATE VIEW`命令在內部實際執行的內容。 這樣做有一些負作用。其中之一就是在PostgreSQL 系統表里的視圖的信息與一般表的信息完全一樣。所以對于查詢分析器來說， 表和視圖之間完全沒有區別。它們是同樣的事物：關系。 ## 38.2.1\. `SELECT`規則如何運轉 `ON SELECT`的規則在最后一步應用于所有查詢，哪怕給出的是一條`INSERT`, `UPDATE` 或 `DELETE`命令。而且與其它命令類型上的規則有不同的語意， 那就是它們在現場修改查詢樹而不是創建一個新的查詢樹。所以先介紹`SELECT`規則。 目前，一個`ON SELECT`規則里只能有一個動作，而且它必須是一個無條件的`INSTEAD` (取代)的`SELECT`動作。有這個限制是為了令規則安全到普通用戶也可以打開它們， 并且它限制`ON SELECT`規則使之行為類似視圖。 本文檔的例子是兩個連接視圖，它們做一些運算并且因此會涉及到更多視圖的使用。 這兩個視圖之一稍后將利用對`INSERT`, `UPDATE`, `DELETE`操作附加規則的方法自定義， 這樣做最終的結果就是這個視圖表現得像一個具有一些特殊功能的真正的表。 這個例子不適合于開始的簡單易懂的例子，從這個例子開始講可能會讓講解變得有些難以理解。 但是用一個覆蓋所有關鍵點的例子來一步一步討論要比舉很多例子搞亂思維好。 比如，需要一個小巧的`min`函數用于返回兩個整數值中較小的那個。用下面方法創建它： ``` CREATE FUNCTION min(integer, integer) RETURNS integer AS $$ SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END $$ LANGUAGE SQL STRICT; ``` 頭兩個規則系統要用到的表如下： ``` 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 -- 轉換成厘米的系數 ); ``` 你可以看到，這些表代表鞋店的數據。 視圖創建為： ``` 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`關系并且在`pg_rewrite`表里增加一個記錄， 告訴系統有一個重寫規則應用于所有范圍表里引用了`shoelace`關系的查詢。 該規則沒有規則條件(將在非`SELECT`規則討論，因為目前的`SELECT` 規則不可能有這些東西)并且它是`INSTEAD`(取代)型的。要注意規則條件與查詢條件不一樣。 規則動作有一個查詢條件。規則的動作是一個查詢樹，這個查詢是樹視圖創建命令中的 `SELECT`語句的一個拷貝。 > **Note:** 你在表`pg_rewrite`里看到的兩個額外的用于`NEW` 和`OLD`的范圍表記錄對`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; ``` 然后把這些交給規則系統。規則系統把范圍表(range table)過濾一遍，檢查一下有沒有適用任何關系的規則。 當為`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; ``` 不過還是有一個區別：子查詢范圍表有兩個額外的記錄`shoelace old` 和`shoelace new`。這些記錄并不直接參與查詢， 因為它們沒有被子查詢的連接樹或者目標列表引用。 重寫器用它們存儲最初出現在引用視圖的范圍表里面的訪問權限檢查。這樣， 執行器仍然會檢查該用戶是否有訪問視圖的合適權限，即使在重寫查詢里面沒有對視圖的直接使用也如此。 這是應用的第一個規則。規則系統繼續檢查頂層查詢里剩下的范圍表記錄(本例中沒有了)， 并且它在加進來的子查詢中遞歸地檢查范圍表記錄，看看其中有沒有引用視圖的 (不過這樣不會擴展`old`或`new`，否則會無窮遞歸下去!)。 在這個例子中，沒有用于`shoelace_data`或`unit`的重寫規則， 所以重寫結束并且上面的就是給規劃器的最終結果。 現在想寫這么一個查詢：這個查詢找出目前在店里有配對鞋帶的鞋子（顏色和長度）， 并且配對的鞋帶數大于或等于二。 ``` 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` 的規則替換到子查詢范圍表里，生成一個最終的三層查詢樹： ``` 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; ``` 最后規劃器會把這個樹壓縮成一個兩層查詢樹：最下層的`SELECT` 將"拖到"中間的`SELECT`中，因為沒有必要分別處理它們。 但是中間的`SELECT`仍然和頂層的分開，因為它包含聚集函數。 如果把它們也拉進來，那它就會修改最頂層`SELECT`的行為， 那可不是想要的。不過，壓縮查詢樹是重寫系統自己不需要關心的優化操作。 ## 38.2.2\. 非 `SELECT` 語句的視圖規則 有兩個查詢樹的細節在上面的視圖規則中沒有涉及到。就是命令類型和結果關系。實際上， 視圖規則不需要命令類型，但是結果關系可能會影響查詢重寫的工作方式， 因為如果結果關系是一個視圖則需要特別的注意。 一個`SELECT`的查詢樹和用于其它命令的查詢樹只有少數幾個區別。顯然， 它們的命令類型不同并且對于`SELECT`之外的命令， 結果關系指向結果將前往的范圍表入口。任何其它東西都完全是一樣的。 所以如果有兩個表`t1`和`t2`分別有字段`a`和`b`， 下面兩個語句的查詢樹： ``` 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`字段。 * 條件表達式比較兩個范圍的字段`a`以尋找相等行。 * 連接樹顯示`t1`和`t2`之間的簡單連接。 結果是，兩個查詢樹生成相似的執行規劃：它們都是兩個表的連接。對于`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`里有點問題：做鏈接的執行器計劃部分不關心連接結果的含義是什么。 它只是產生一個行的結果集。一個是`SELECT` 命令而另一個是`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里視圖的強大能力 上面演示了規則系統如何融合視圖定義到初始查詢樹中去。在第二個例子里， 一個簡單的對視圖的`SELECT`創建了一個四表聯合的查詢樹 (`unit`以不同的名稱用了兩次)。 在規則系統里實現視圖的好處是，規劃器在一個查詢樹里擁有所有信息： 應該掃描哪個表+表之間的關系+視圖的資格限制+初始查詢的資格(條件)。 并且仍然是在最初的查詢已經是一個視圖的聯合的情況下。現在規劃器必須決定執行查詢的最優路徑。 規劃器擁有越多信息，它的決策就越好。并且PostgreSQL 里的規則系統的實現保證這些信息是此時能獲得的有關該查詢的所有信息。 ## 38.2.4\. 更新一個視圖 如果視圖命名為`INSERT`, `UPDATE`,`DELETE` 的目標關系會怎樣?在完成上面描述的替換之后，就有一個這樣的查詢樹： 結果關系指向一個是子查詢的范圍表記錄，這樣可不能運行。 在PostgreSQL中有幾種方式支持更新一個視圖的外觀。 如果子查詢從一個單一的基本關系選擇或者足夠簡單，重寫可以自動的用底層的基本關系替代子查詢， 所以`INSERT`，`UPDATE` 或 `DELETE` 以適當的方式應用于基本關系。視圖因為"足夠簡單"而被稱為_可自動更新的_。 有關這種可以自動更新的視圖的更詳細的信息請參閱[CREATE VIEW](#calibre_link-473)。 或者，操作可能通過一個用戶提供的在視圖上的`INSTEAD OF`觸發器處理。 重寫工作在這種情況下略有不同。對于`INSERT`， 重寫并不對視圖做什么，讓它作為查詢的結果關系。對于`UPDATE`和 `DELETE`，重寫仍然需要擴展視圖查詢，產生命令將要更新或刪除的"old" 行。所以，視圖正常擴展，但是另外一個不擴展的范圍表條目添加到查詢中， 代表視圖作為結果關系。 現在出現的問題是如何識別出視圖中要更新的行。回想當結果關系是一個表時， 一個特殊的CTID條目被添加到目標列表，以識別出要被更新的行的物理位置。 如果結果關系是一個視圖這將不會工作，因為視圖沒有任何CTID， 因為它的行沒有真實的物理位置。相反，對于`UPDATE`或 `DELETE`操作，一個特殊的`wholerow` 條目被添加到目標列表，它擴大到包含視圖的所有列。執行器使用這個值提供"old" 行到`INSTEAD OF`觸發器。由觸發器基于舊行和新行值找出來的需要更新什么。 另外一個可能是用戶定義`INSTEAD`規則，為視圖上的`INSERT`, `UPDATE`, 和 `DELETE`命令指定替代動作。 這些規則將重寫命令，通常進入一個命令更新一個或更多的表，而不是視圖。 這是下一節的主題。 請注意，首先評估規則，在規劃和執行之前重寫原先的查詢。因此， 如果一個視圖有`INSTEAD OF`觸發器，也有在`INSERT`, `UPDATE`, 或 `DELETE`上的規則，那么規則將被首先評估， 根據評估結果，觸發器可能不會使用。 在一個簡單視圖上的`INSERT`, `UPDATE`, 或 `DELETE`查詢的自動重寫總是最后嘗試。因此， 如果一個視圖有規則或觸發器，他們將覆蓋自動更新視圖的缺省行為。 如果視圖上沒有`INSTEAD`規則或`INSTEAD OF`觸發器， 并且重寫不能作為一個在底層基本關系上的更新自動重寫查詢， 那么將拋出一個錯誤，因為執行器不能像這樣的更新視圖。