PostgreSQL
上一页 第四十二章. Postgres 规则系统 下一页

视图和规则系统

Postgres 里视图的实现

Postgres 里的视图是通过规则系统来实现的．实际上下面的命令 
    CREATE VIEW myview AS SELECT * FROM mytab;
和下面两条命令 
    CREATE TABLE myview (same attribute list as for mytab);
    CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD
        SELECT * FROM mytab;
之间绝对没有区别，因为这就是  CREATE VIEW 命令在内部实际执行的内容．这样做有一些负作用．其中之一就是在 Postgres 系统表里的视图的信息与一般表的信息完全一样．所以对于查询分析器来说，表和视图之间完全没有区别．它们是同样的事物－－关系．这就是目前很重要的一点．

SELECT 规则如何工作

ON SELECT 的规则在最后一步应用于所有查询，哪怕给出的命令是一条  INSERT，UPDATE 或 DELETE．而且与其他（规则）有不同的语意，那就是它们在实地修改分析树而不是创建一个新的（分析树）．所以我们先介绍 SELECT 的规则．

目前，这里只可能发生一个动作（action）而且它必须是一个 INSTEAD （取代了）的 SELECT 动作．有这个限制是为了令规则安全到普通用户也可以打开它们，并且它对真正的视图规则做 ON SELECT 规则限制．

本文档的例子是两个联合视图，它们做一些运算并且会涉及到更多视图的使用．这两个视图之一稍后将利用对 INSERT，UPDATE 和 DELETE 操作附加规则的方法客户化，这样做最终的结果就会是这个视图表现得象一个具有一些特殊功能的真正的表．这可不是一个适合于开始的简单易懂的例子，从这个例子开始讲可能会让我们的讲解变得有些难以理解．但是我们认为用一个覆盖所有关键点的例子来一步一步讨论要比举很多例子搞乱思维好多了．

在本例子中用到的数据库名是 al_bundy．你很快就会明白为什么叫这个名字．而且这个例子需要安装过程语言 PL/pgSQL ，因为我们需要一个小巧的 min() 函数用于返回两个整数值中的小的那个．我们用下面方法创建它 

    CREATE FUNCTION min(integer, integer) RETURNS integer AS
        'BEGIN
            IF $1 < $2 THEN
                RETURN $1;
            END IF;
            RETURN $2;
        END;'
    LANGUAGE 'plpgsql';
我们头两个规则系统要用到的真实的表的描述如下： 
    CREATE TABLE shoe_data (
        shoename   char(10),      -- primary key
        sh_avail   integer,       -- available # of pairs
        slcolor    char(10),      -- preferred shoelace color
        slminlen   float,         -- miminum shoelace length
        slmaxlen   float,         -- maximum shoelace length
        slunit     char(8)        -- length unit
    );

    CREATE TABLE shoelace_data (
        sl_name    char(10),      -- primary key
        sl_avail   integer,       -- available # of pairs
        sl_color   char(10),      -- shoelace color
        sl_len     float,         -- shoelace length
        sl_unit    char(8)        -- length unit
    );

    CREATE TABLE unit (
        un_name    char(8),       -- the primary key
        un_fact    float          -- factor to transform to cm
    );
我想我们都需要穿鞋子，因而上面这些数据都是很有用的数据．当然，有那些不需要鞋带的鞋子，但是不会让 AL 的生活变得更轻松，所以我们忽略之．

视图创建为 

    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 命令（也是我们用到的最简单的一个） 将创建一个关系/表 -- 鞋带（relation shoelace ）并且在 pg_rewrite 表里增加一个记录，告诉系统有一个重写规则应用于所有索引了鞋带关系（relation shoelace）的查询．该规则没有规则资格（将在非 SELECT 规则讨论，因为目前的 SELECT 规则不可能有这些东西）并且它是 INSTEAD （取代）型的．要注意规则资格与查询资格不一样！这个规则动作（action）有一个资格．

规则动作（action）是一个查询树，实际上是在创建视图的命令里的 SELECT 语句的一个拷贝．

注意：你在表 pg_rewrite 里看到的两个额外的用于 NEW 和 OLD 范围表的记录（因历史原因，在打印出来的查询树里叫 *NEW* 和 *CURRENT* ）对 SELECT 规则不感兴趣．
现在我们填充 unit，shoe_data 和 shoelace_data 并且 Al 生平第一次键入了 SELECT 命令： 
    al_bundy=> INSERT INTO unit VALUES ('cm', 1.0);
    al_bundy=> INSERT INTO unit VALUES ('m', 100.0);
    al_bundy=> INSERT INTO unit VALUES ('inch', 2.54);
    al_bundy=> 
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh1', 2, 'black', 70.0, 90.0, 'cm');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh2', 0, 'black', 30.0, 40.0, 'inch');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
    al_bundy=> 
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl1', 5, 'black', 80.0, 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl2', 6, 'black', 100.0, 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl3', 0, 'black', 35.0 , 'inch');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl4', 8, 'black', 40.0 , 'inch');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl5', 4, 'brown', 1.0 , 'm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl6', 0, 'brown', 0.9 , 'm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl7', 7, 'brown', 60 , 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl8', 1, 'brown', 40 , 'inch');
    al_bundy=> 
    al_bundy=> 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)
这是 Al 可以在我们的视图上做的最简单的 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;
然后把这些交给规则系统．规则系统把可排列元素（rangetable）过滤一遍，检查一下在 pg_rewrite 表里面有没有适用该关系的任何规则．当为 shoelace 处理可排列元素时（到目前为止唯一的一个），它会发现分析树里有规则 '_RETshoelace' 
    SELECT s.sl_name, s.sl_avail,
           s.sl_color, s.sl_len, s.sl_unit,
           float8mul(s.sl_len, u.un_fact) AS sl_len_cm
      FROM shoelace *OLD*, shoelace *NEW*,
           shoelace_data s, unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
注意分析器已经把（SQL里的）计算和资格换成了相应的函数．但实际上这没有改变什么．重写的第一步是把两个可排列元素归并在一起．结果生成的分析树是 
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u;
第二步把资格的规则动作追加到分析树里面去，结果是 
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
第三步把分析树里的所有变量用规则动作里对应的目标列表达式替换掉，这些变量是引用了可排列元素（目前来说是正在处理的 shoelace ）的变量．这就生成了最后的查询 
    SELECT s.sl_name, s.sl_avail, 
           s.sl_color, s.sl_len, 
           s.sl_unit, float8mul(s.sl_len, u.un_fact) AS sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
把这些转换回人类可能使用的 SQL 语句 
    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;
这是应用的第一个规则．当做完这些后，可排列元素就增加了．所以规则系统继续检查范围表入口．下一个是第2个 （shoelace *OLD*）． shoelace （鞋带）关系有一个规则，但这个可排列元素没有被任何分析树里的变量引用，所以被忽略．因为所有剩下的可排列元素入口要么是在 pg_rewrite 表里面没有记录，要么是没有引用，因而到达重排列元素结尾．所以重写结束，因而上面的结果就是给优化器的最终结果．优化器忽略那些在分析树里多余的没有被变量引用的可排列元素，并且由规划器/优化器生成的（运行）规划将和 Al 在上面键入的 SELECT 查询一样，而不是视图选择．

现在我们让 Al 面对这样一个问题：Blues 兄弟到了他的鞋店想买一双新鞋，而且 Blues 兄弟想买一样的鞋子．并且要立即就穿上，所以他们还需要鞋带．

Al 需要知道鞋店里目前那种鞋有合适的鞋带（颜色和尺寸）以及完全一样的配置的库存是否大于或等于两双．我们告诉他如何做，然后他问他的数据库： 

    al_bundy=> 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)
Al 是鞋的专家，知道只有 sh1 的类型会适用（sl7鞋带是棕色的，而与棕色的鞋带匹配的鞋子是 Blues 兄弟从来不穿的）．

这回分析器的输出是分析树 

    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 int4ge(shoe_ready.total_avail, 2);
应用的第一个规则将是用于 shoe_ready 关系的，结果是生成分析树 
    SELECT rsh.shoename, rsh.sh_avail,
           rsl.sl_name, rsl.sl_avail,
           min(rsh.sh_avail, rsl.sl_avail) AS total_avail
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl
     WHERE int4ge(min(rsh.sh_avail, rsl.sl_avail), 2)
       AND (bpchareq(rsl.sl_color, rsh.slcolor)
            AND float8ge(rsl.sl_len_cm, rsh.slminlen_cm)
            AND float8le(rsl.sl_len_cm, rsh.slmaxlen_cm)
           );
实际上，资格/条件里的 AND 子句将是拥有左右表达式的操作符节点．但那样会把可读性降低，而且还有更多规则要附加．所以我只是把它们放在一些圆括号里，将它们按出现顺序分成逻辑单元，然后我们继续对付用于 shoe （鞋）关系的规则，因为它是引用了的下一个可排列元素并且有一条规则．应用规则后的结果是 
    SELECT sh.shoename, sh.sh_avail,
           rsl.sl_name, rsl.sl_avail,
           min(sh.sh_avail, rsl.sl_avail) AS total_avail,
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, shoe *OLD*,
           shoe *NEW*, shoe_data sh,
           unit un
     WHERE (int4ge(min(sh.sh_avail, rsl.sl_avail), 2)
            AND (bpchareq(rsl.sl_color, sh.slcolor)
                 AND float8ge(rsl.sl_len_cm, 
                              float8mul(sh.slminlen, un.un_fact))
                 AND float8le(rsl.sl_len_cm, 
                              float8mul(sh.slmaxlen, un.un_fact))
                )
           )
       AND bpchareq(sh.slunit, un.un_name);
最后，我们把已经熟知的用于 shoelace （鞋带）的规则附加上去（这回我们在一个更复杂的分析树上）得到 
    SELECT sh.shoename, sh.sh_avail,
           s.sl_name, s.sl_avail,
           min(sh.sh_avail, s.sl_avail) AS total_avail
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, shoe *OLD*,
           shoe *NEW*, shoe_data sh,
           unit un, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE (    (int4ge(min(sh.sh_avail, s.sl_avail), 2)
                 AND (bpchareq(s.sl_color, sh.slcolor)
                      AND float8ge(float8mul(s.sl_len, u.un_fact), 
                                   float8mul(sh.slminlen, un.un_fact))
                      AND float8le(float8mul(s.sl_len, u.un_fact), 
                                   float8mul(sh.slmaxlen, un.un_fact))
                     )
                )
            AND bpchareq(sh.slunit, un.un_name)
           )
       AND bpchareq(s.sl_unit, u.un_name);
同样，我们把它归结为一个与最终的规则系统输出等效的真实 SQL 语句： 
    SELECT sh.shoename, sh.sh_avail,
           s.sl_name, s.sl_avail,
           min(sh.sh_avail, s.sl_avail) AS total_avail
      FROM shoe_data sh, shoelace_data s, unit u, unit un
     WHERE min(sh.sh_avail, s.sl_avail) >= 2
       AND s.sl_color = sh.slcolor
       AND s.sl_len * u.un_fact >= sh.slminlen * un.un_fact
       AND s.sl_len * u.un_fact <= sh.slmaxlen * un.un_fact
       AND sh.sl_unit = un.un_name
       AND s.sl_unit = u.un_name;
递归的处理规则将把一个从视图的 SELECT 改写为一个分析树，这样做等效于如果没有视图存在时 Al 不得不键入的（SQL）命令．
注意： 目前规则系统中没有用于视图规则递归终止机制（只有用于其他规则的）．这一点不会造成太大的损害，因为把这个（规则）无限循环（把后端摧毁，直到耗尽内存）的唯一方法是创建表然后手工用 CREATE RULE 命令创建视图规则，这个规则是这样的：一个从其他（表/视图）选择（select）的视图选择（select）了它自身．如果使用了 CREATE VIEW ，这一点是永远不会发生的，因为第二个关系不存在因而第一个视图不能从第二个里面选择（select）．

非 SELECT 语句的视图规则

有两个分析树的细节我们在上面的视图规则中没有涉及到．就是命令类型和结果关系．实际上，视图规则不需要这些信息．

一个 SELECT 的分析树和用于其他命令的分析树只有少数几个区别．显然它们有另一个命令类型并且这回结果关系指向生成结果的可排列元素入口．任何其东西都完全是一样的．所以如果有两个表 t1 和 t2 分别有字段 a 和 b ，下面两个语句的分析树 

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

    UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
几乎是一样的． 结果是，两个分析树生成相似的执行规划．它们都是两个表的联合．对于 UPDATE 语句来说，优化器把 t1 缺失的字段追加到目标列因而最终分析树看起来象 
    UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a;
因此执行器在联合上运行的结果和下面语句 
    SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
是完全一样的．但是在 UPDATE 里有点问题．执行器不关心它正在处理的从联合出来的结果的含义是什么．它只是产生一个行的结果集．一个是 SELECT 命令而另一个是 UPDATE 命令的区别是由执行器的调用者控制的．该调用者这时还知道（查看分析树）这是一个 UPDATE，而且它还知道结果要记录到表 t1 里去．但是现有的666行记录中的哪一行要被新行取代呢？被执行的（查询）规划是一个带有资格（条件）的联合，该联合可能以未知顺序生成 0 到 666 间任意数量的行．

要解决这个问题，在 UPDATE 和 DELETE 语句的目标列表里面增加了另外一个入口．当前的记录 ID（ctid）．这是一个有着特殊特性的系统字段．它包含行在（存储）块中的（存储）块数和位置信息．在已知表的情况下，ctid 可以通过简单地查找某一数据块在一个 1.5GB 大小的包含成百万条记录的表里面查找某一特定行．在把 ctid 加到目标列表中去以后，最终的结果可以定义为 

    SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
现在，另一个 Postgres 的细节进入到这个阶段里了．这时，表的行还没有被覆盖，这就是为什么 ABORT TRANSACTION 速度快的原因．在一个 UPDATE 里，新的结果行插入到表里（在通过 ctid 查找之后）并且把 ctid 指向的 cmax 和 xmax 入口的行的记录头设置为当前命令计数器和当前交易ID．这样旧的行就被隐藏起来并且在事务提交之后"吸尘器"（vacumm cleaner）就可以真正把它们删除掉．

知道了这些，我们就可以简单的把视图的规则应用到任意命令中．它们（视图和命令）没有区别．

Postgres 里视图的强大能力

上面演示了规则系统如何融合到视图定义的初始分析树中去．在第二个例子里，一个简单的对视图的 SELECT 创建了一个4个表联合的分析树（unit 以不同的名称用了两次）．

益处

在规则系统里实现视图的好处是优化器在一个分析树里拥有所有信息：应该扫描哪个表 + 表之间的关系 + 视图的资格限制 + 初始查询的资格（条件）．并且依然是在最初的查询已经是一个视图的联合的情况下．现在优化器必须决定执行查询的最优路径．优化器拥有越多信息，它的决策就越好．并且 Postgres 里的规则系统的实现保证这些信息是目前能获得的所有信息．

考虑

很长一段时间里，Postgres 规则系统被认为是有问题的．规则的使用是不被推荐的而且能工作的部分只有视图规则．而且这些视图规则还常常犯毛病，因为规则系统不能在除 SELECT 语句外的语句里应用它们（例如用从一个视图来的数据 UPDATE 就不能工作）．

在那段时间里，开发工作继续进行，许多新特性加入到分析器和优化器里．规则系统的功能越来越陈旧而且越来越难以修复它们．

从 6.4 起，某个人（译注：感谢 Jan ）关起门，深吸一口气把所有这些烂东西彻底修理了一便．结果就是本章描述的规则系统．但是还有一些无法处理的构造和一些失效的地方，主要原因是这些东西现在不被 Postgres 查询优化器支持．

    带聚集字段的视图有很大问题．在资格列里的聚集表达式必须通过子查询使用．目前不可能做两个视图的联合查询 -- 每个都有一个聚集字段，并且与资格列里的两个聚集的结果进行比较．但我们可能把这些聚集表达式放到函数里，通过合适的参数在视图定义中使用它们．

    联合（union）的视图当前不被支持．尽管我们很容易把一个简单的SELECT 重写成联合（union）．但是如果该视图是一个正在做更新的联合的一部分时就会有麻烦．

    视图里的 ORDER BY 子句不被支持．

    视图里的 DISTINCT 不被支持．
为什么优化器不能处理那些分析器因 SQL 语法限制生成的"不应该生成"分析树？我们也没有充分的理由．作者希望这些问题在将来会消失．

如此实现的副作用

使用上面描述的规则系统来实现视图有着有趣的副作用．下面的（命令）看起来不会工作： 
    al_bundy=> INSERT INTO shoe (shoename, sh_avail, slcolor)
    al_bundy->     VALUES ('sh5', 0, 'black');
    INSERT 20128 1
    al_bundy=> SELECT shoename, sh_avail, slcolor FROM shoe_data;
    shoename  |sh_avail|slcolor   
    ----------+--------+----------
    sh1       |       2|black     
    sh3       |       4|brown     
    sh2       |       0|black     
    sh4       |       3|brown     
    (4 rows)
有趣的事情是 INSERT 的返回码给我们一个对象标识（OID）并且告诉我们插入了一行．但该行没有在 shoe_data 里出现．往数据库目录里看时我们可以发现，用于视图关系 shoe 的数据库文件看来现在有了数据块．实际情况正是如此．

我们还可以使用一条 DELETE 命令，如果该命令没有（资格）条件，它会告诉我们有一行被删除了并且下一次清理时将把文件复位为零尺寸．

这种现象的原因是 INSERT 生成的分析树没有在任何变量里引用 shoe （鞋）关系．目标列表只包含常量值．所以不会附加任何规则，查询不加修改地进入执行插入该行． DELETE 时完全一样．

要改变这些问题，我们可以定义一些规则用以改变 非－SELECT 查询的特性．这是下一章的内容．

上一页 首页 下一页
Postgres 规则系统 开头 INSERT, UPDATE 和 DELETE 的规则
�