PostgreSQL 視圖和規(guī)則系統(tǒng)

2021-09-03 16:38 更新
40.2.1. SELECT規(guī)則如何工作
40.2.2. 非SELECT語句中的視圖規(guī)則
40.2.3. PostgreSQL中視圖的能力
40.2.4. 更新一個(gè)視圖

PostgreSQL中的視圖是通過規(guī)則系統(tǒng)來實(shí)現(xiàn)的。事實(shí)上，下面的命令

CREATE VIEW myview AS SELECT * FROM mytab;

與下面兩個(gè)命令相比沒有不同：

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

因?yàn)檫@就是CREATE VIEW命令在內(nèi)部所作的。 這樣做有一些副作用。其中之一就是在PostgreSQL系統(tǒng)目錄中的視圖信息與表的信息完全一樣。所以對于解析器來說，表和視圖之間完全沒有區(qū)別。它們是同樣的事物：關(guān)系。

40.2.1. SELECT規(guī)則如何工作

規(guī)則ON SELECT被應(yīng)用于所有查詢作為最后一步，即使給出的是一條INSERT、UPDATE或DELETE命令。而且它們與其他命令類型上的規(guī)則有著不同的語義，它們會就地修改查詢樹而不是創(chuàng)建一個(gè)新的查詢樹。因此我們首先描述SELECT規(guī)則。

目前，一個(gè)ON SELECT規(guī)則中只能有一個(gè)動(dòng)作， 而且它必須是一個(gè)無條件的INSTEAD的SELECT動(dòng)作。 這個(gè)限制是為了令規(guī)則足夠安全，以便普通用戶也可以打開它們，并且它限制ON SELECT規(guī)則使之行為類似視圖。

本章的例子是兩個(gè)連接視圖，它們做一些運(yùn)算并且某些更多視圖會輪流使用它們。最前面的兩個(gè)視圖之一后面將利用對INSERT、UPDATE和DELETE操作增加規(guī)則的方法被自定義，這樣最終結(jié)果將是一個(gè)視圖，它表現(xiàn)得像一個(gè)具有魔力的真正的表。這個(gè)例子不適合于作為簡單易懂的例子，它可能會讓本章更難懂。但是用一個(gè)覆蓋所有關(guān)鍵點(diǎn)的例子來一步一步討論要比舉很多例子搞亂思維好。

在前兩個(gè)規(guī)則系統(tǒng)描述中我們需要真實(shí)表是：

CREATE TABLE shoe_data (
    shoename   text,          -- 主鍵
    sh_avail   integer,       -- 可用的雙數(shù)
    slcolor    text,          -- 首選的鞋帶顏色
    slminlen   real,          -- 最小鞋帶長度
    slmaxlen   real,          -- 最大鞋帶長度
    slunit     text           -- 長度單位
);

CREATE TABLE shoelace_data (
    sl_name    text,          -- 主鍵
    sl_avail   integer,       -- 可用的雙數(shù)
    sl_color   text,          -- 鞋帶顏色
    sl_len     real,          -- 鞋帶長度
    sl_unit    text           -- 長度單位
);

CREATE TABLE unit (
    un_name    text,          -- 主鍵
    un_fact    real           -- 轉(zhuǎn)換到厘米的參數(shù)
);

如你所見，它們表示鞋店的數(shù)據(jù)。

視圖被創(chuàng)建為：

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,
           least(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;

創(chuàng)建shoelace視圖的CREATE VIEW命令（也是最簡單的一個(gè)）將創(chuàng)建一個(gè)shoelace關(guān)系和一個(gè)pg_rewrite項(xiàng)， 這個(gè)pg_rewrite項(xiàng)說明有一個(gè)重寫規(guī)則，只要一個(gè)查詢的范圍表中引用了關(guān)系 shoelace，就必須應(yīng)用它。該規(guī)則沒有規(guī)則條件（稍后和非SELECT規(guī)則一起討論，因?yàn)槟壳暗?code class="command">SELECT規(guī)則不能有規(guī)則條件）并且它是INSTEAD規(guī)則。要注意規(guī)則條件與查詢條件不一樣。我們的規(guī)則的動(dòng)作有一個(gè)查詢條件。該規(guī)則的動(dòng)作是一個(gè)查詢樹，這個(gè)查詢是視圖創(chuàng)建命令中的SELECT語句的一個(gè)拷貝。

注意

你在pg_rewrite項(xiàng)中看到的兩個(gè)額外的用于NEW和OLD的范圍表項(xiàng)不是SELECT規(guī)則感興趣的東西。

現(xiàn)在我們填充unit、shoe_data和shoelace_data，并且在視圖上運(yùn)行一個(gè)簡單的查詢：

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，所以我們用這次機(jī)會來解釋視圖規(guī)則的基本要素。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;

然后這將被交給規(guī)則系統(tǒng)。規(guī)則系統(tǒng)遍歷范圍表，檢查有沒有可用于任何關(guān)系的規(guī)則。在為shoelace（到目前為止的唯一一個(gè)）處理范圍表時(shí)， 它會發(fā)現(xiàn)查詢樹里有_RETURN規(guī)則：

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;

要擴(kuò)展該視圖，重寫器簡單地創(chuàng)建一個(gè)子查詢范圍表項(xiàng)，它包含規(guī)則的動(dòng)作的查詢樹，然后用這個(gè)范圍表記錄取代原來引用視圖的那個(gè)。作為結(jié)果的重寫后的查詢樹幾乎與你鍵入的那個(gè)一樣：

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;

不過有一個(gè)區(qū)別：子查詢的范圍表有兩個(gè)額外的項(xiàng)shoelace old和shoelace new。這些項(xiàng)并不直接參與到查詢中，因?yàn)樗鼈儧]有被子查詢的連接樹或者目標(biāo)列表引用。重寫器用它們存儲最初出現(xiàn)在引用視圖的范圍表項(xiàng)中表達(dá)的訪問權(quán)限檢查信息。以這種方式，執(zhí)行器仍然會檢查該用戶是否有訪問視圖的正確權(quán)限，盡管在重寫后的查詢中沒有對視圖的直接使用。

這是被應(yīng)用的第一個(gè)規(guī)則。規(guī)則系統(tǒng)將繼續(xù)檢查頂層查詢里剩下的范圍表項(xiàng)（本例中沒有了），并且它將遞歸的檢查增加的子查詢中的范圍表項(xiàng)，看看其中有沒有引用視圖的（不過這樣不會擴(kuò)展old或new — 否則我們會得到無限遞歸?。Ｔ谶@個(gè)例子中，沒有用于shoelace_data或unit的重寫規(guī)則，所以重寫結(jié)束并且上面得到的就是給規(guī)劃器的最終結(jié)果。

現(xiàn)在我們想寫一個(gè)查詢，它找出目前在店里哪些鞋子有匹配的（顏色和長度）鞋帶并且完全匹配的鞋帶雙數(shù)大于等于二。

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;

第一個(gè)被應(yīng)用的規(guī)則將是用于shoe_ready的規(guī)則并且它會導(dǎo)致查詢樹：

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,
               least(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的規(guī)則被替換到子查詢的范圍表中，得到一個(gè)三層的最終查詢樹：

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,
               least(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;

這看起來是低效的，但是計(jì)劃器會通過“pulling up”子查詢將其折疊成一個(gè)單層查詢樹，然后它會計(jì)劃聯(lián)接， 就像我們手動(dòng)寫出來一樣。 因此，折疊查詢樹是重寫系統(tǒng)本身不必關(guān)心的一種優(yōu)化。

40.2.2. 非SELECT語句中的視圖規(guī)則

有兩個(gè)查詢樹的細(xì)節(jié)在上面的視圖規(guī)則的描述中沒有涉及。它們是命令類型和結(jié)果關(guān)系。實(shí)際上，視圖規(guī)則不需要命令類型，但是結(jié)果關(guān)系可能會影響查詢重寫器工作的方式，因?yàn)槿绻Y(jié)果關(guān)系是一個(gè)視圖，我們需要采取特殊的措施。

一個(gè)SELECT的查詢樹和其它命令的查詢樹之間很少的幾處不同。顯然，它們有不同的命令類型并且對于SELECT之外的命令，結(jié)果關(guān)系指向結(jié)果將進(jìn)入的范圍表項(xiàng)。其它所有東西都完全相同。所以如果有兩個(gè)表t1和t2分別有列a和 b，下面兩個(gè)語句的查詢樹：

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的項(xiàng)。

  • 目標(biāo)列表包含一個(gè)變量，該變量指向表t2的范圍表項(xiàng)的列b。

  • 條件表達(dá)式比較兩個(gè)范圍表項(xiàng)的列a以尋找相等。

  • 連接樹展示了t1和t2之間的一次簡單連接。

結(jié)果是，兩個(gè)查詢樹生成相似的執(zhí)行計(jì)劃：它們都是兩個(gè)表的連接。 對于UPDATE語句，規(guī)劃器把t1缺失的列加到目標(biāo)列并且最終查詢樹讀起來是：

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

因此在連接上運(yùn)行的執(zhí)行器將產(chǎn)生完全相同的結(jié)果集：

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

但是在UPDATE中有個(gè)小問題：執(zhí)行器計(jì)劃中執(zhí)行連接的部分不關(guān)心連接的結(jié)果的含義。它只是產(chǎn)生一個(gè)行的結(jié)果集。一個(gè)是SELECT命令而另一個(gè)是由執(zhí)行器中的更高層處理的UPDATE命令，在那里執(zhí)行器知道這是一個(gè)UPDATE，并且它知道這個(gè)結(jié)果應(yīng)該進(jìn)入表t1。但是這里的哪些行必須被新行替換呢？

要解決這個(gè)問題，在UPDATE和DELETE語句的目標(biāo)列表里面增加了另外一個(gè)項(xiàng)：當(dāng)前元組 ID（CTID）。 這是一個(gè)系統(tǒng)列，它包含行所在的文件塊編號和在塊中的位置。在已知表的情況下，CTID可以被用來檢索要被更新的t1的原始行。在添加CTID到目標(biāo)列之后，該查詢實(shí)際看起來像：

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

現(xiàn)在，另一個(gè)PostgreSQL的細(xì)節(jié)進(jìn)入到這個(gè)階段了。表中的舊行還沒有被覆蓋，這就是為什么ROLLBACK很快的原因。在一個(gè)UPDATE中，新的結(jié)果行被插入到表中（在剝除CTID之后），并且把CTID指向的舊行的行頭部中的 cmax和xmax項(xiàng)設(shè)置為當(dāng)前命令計(jì)數(shù)器和當(dāng)前事務(wù) ID 。這樣舊的行就被隱藏起來，并且在事務(wù)提交之后 vacuum 清理器就可以最終移除死亡的行。

知道了所有這些，我們就可以用完全相同的方式簡單地把視圖規(guī)則應(yīng)用到任意命令中。沒有任何區(qū)別。

40.2.3. PostgreSQL中視圖的能力

上文演示了規(guī)則系統(tǒng)如何把視圖定義整合到原始的查詢樹中。在第二個(gè)例子中，一個(gè)來自于一個(gè)視圖的簡單SELECT創(chuàng)建了一個(gè)四表連接（unit以不同的名字被用了兩次）的最終查詢樹。

用規(guī)則系統(tǒng)實(shí)現(xiàn)視圖的好處是，規(guī)劃器擁有關(guān)于哪些表必須被掃描、這些表之間的聯(lián)系、來自于視圖的限制性條件、一個(gè)單一查詢樹中原始查詢的條件等所有信息。當(dāng)原始查詢已經(jīng)是一個(gè)視圖上的連接時(shí)仍然是這樣。規(guī)劃器必須決定執(zhí)行查詢的最優(yōu)路徑，而且規(guī)劃器擁有越多信息，該決定就越好。并且PostgreSQL中實(shí)現(xiàn)的規(guī)則系統(tǒng)保證這些信息是此時(shí)能獲得的有關(guān)該查詢的所有信息。

40.2.4. 更新一個(gè)視圖

如果視圖是INSERT、UPDATE或DELETE的目標(biāo)關(guān)系會怎樣？使用上文所述的替換將給出一個(gè)查詢樹，其中的結(jié)果關(guān)系指向一個(gè)子查詢范圍表項(xiàng)，這樣無法工作。不過，PostgreSQL中有幾種方法來支持更新視圖。

如果子查詢從一個(gè)單一基本關(guān)系選擇并且該關(guān)系足夠簡單，重寫器會自動(dòng)地把該子查詢替換成底層的基本關(guān)系，這樣INSERT、UPDATE或DELETE會被以適當(dāng)?shù)姆绞綉?yīng)用到該基本關(guān)系。其中“足夠簡單”的視圖被稱為自動(dòng)可更新。有關(guān)這種可以被自動(dòng)更新的視圖類別的詳細(xì)信息，請見 CREATE VIEW 。

或者，該操作可以被定義在視圖上的一個(gè)用戶提供的INSTEAD OF觸發(fā)器處理。在這種情況下重寫工作有一點(diǎn)點(diǎn)不同。對于INSERT，重寫器對視圖什么也不做，讓它作為查詢的結(jié)果關(guān)系。對于UPDATE和DELETE，仍有必要擴(kuò)展該視圖查詢來產(chǎn)生命令將嘗試更新或刪除的 “舊”行。因此該視圖被按照通常的方式擴(kuò)展，但是另一個(gè)未被擴(kuò)展的范圍表項(xiàng)會被增加到查詢來表示該視圖會盡其所能作為結(jié)果關(guān)系。

現(xiàn)在出現(xiàn)的問題是如何標(biāo)識在視圖中要被更新的行?；貞浺幌?，當(dāng)結(jié)果關(guān)系是一個(gè)表時(shí)，一個(gè)特殊的CTID項(xiàng)會被加入到目標(biāo)列表來標(biāo)識要被更新的行的物理位置。如果結(jié)果關(guān)系是一個(gè)視圖這就行不通，因?yàn)橐粋€(gè)視圖根本就沒有CTID，它的行沒有實(shí)際的物理位置。對于一個(gè)UPDATE或DELETE操作，一個(gè)特殊的 wholerow項(xiàng)會被增加到目標(biāo)列表中，它會擴(kuò)展來包括來自該視圖的所有列。執(zhí)行器使用這個(gè)值來提供“舊”行給INSTEAD OF觸發(fā)器?，F(xiàn)在就輪到觸發(fā)器來基于新舊行值來找出要更新什么了。

另外一種可能性是讓用戶定義INSTEAD規(guī)則，這種規(guī)則指定對視圖上的INSERT\UPDATE和DELETE命令的替代動(dòng)作。這些規(guī)則將重寫該命令，通常是重寫成一個(gè)更新一個(gè)或多個(gè)表（而不是視圖）的命令。這是第 40.4 節(jié)的主題。

注意規(guī)則會首先被計(jì)算，然后在原始查詢被規(guī)劃和執(zhí)行之前重寫它。因此，如果一個(gè)視圖上同時(shí)有INSTEAD OF觸發(fā)器和INSERT、UPDATE或DELETE規(guī)則，那么首先會計(jì)算規(guī)則，然后根據(jù)其結(jié)果決定是否執(zhí)行觸發(fā)器，觸發(fā)器可能完全都不會被使用。

Automatic rewriting of an 在一個(gè)簡單視圖上的INSERT、UPDATE或DELETE查詢的自動(dòng)重寫總是在最后嘗試。因此，如果一個(gè)視圖有規(guī)則或觸發(fā)器，它們將重載自動(dòng)可更新視圖的默認(rèn)行為。

如果對該視圖沒有INSTEAD規(guī)則或INSTEAD OF觸發(fā)器，并且重寫器不能自動(dòng)地把該查詢重寫成一個(gè)底層基本關(guān)系上的更新，將會拋出一個(gè)錯(cuò)誤，因?yàn)閳?zhí)行器不能更新一個(gè)這樣的視圖。


以上內(nèi)容是否對您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號