PostgreSQL 8.2.3 中文文档
后退快退章35. 规则系统快进前进

35.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 系统表里的视图的信息与一般表的信息完全一样。所以对于查询分析器来说,表和视图之间完全没有区别。它们是同样的事物:关系。

35.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 语句的一个拷贝。

【注意】你在表 pg_rewrite 里看到的两个额外的用于 NEWOLD 的范围表记录(因历史原因,在打印出来的查询树里叫 *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_dataunit 的重写规则,所以重写结束并且上面的就是给规划器的最终结果。

现在想写这么一个查询:这个查询找出目前在店里有配对鞋带的鞋子,并且配对的鞋带数大于或等于二。

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;

与上面类似,用于 shoeshoelace 的规则替换到子查询范围表里,生成一个最终的三层查询树:

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 的行为,那可不是想要的。不过,压缩查询树是重写系统自己不需要关心的优化操作。

35.2.2. 非 SELECT 语句的视图规则

有两个查询树的细节在上面的视图规则中没有涉及到。就是命令类型和结果关系。实际上,视图规则不需要这些信息。

一个 SELECT 的查询树和用于其它命令的查询树只有少数几个区别。显然,它们的命令类型不同并且对于 SELECT 之外的命令,结果关系指向结果将前往的范围表入口。任何其它东西都完全是一样的。所以如果有两个表 t1t2 分别有字段 ab ,下面两个语句的查询树

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

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

几乎是一样的。特别是:

结果是,两个查询树生成相似的执行规划:它们都是两个表的连接。对于 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 里去。但是现有的记录中的哪一行要被新行取代呢?

要解决这个问题,在 UPDATEDELETE 语句的目标列表里面增加了另外一个入口。当前的行 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 指向的旧数据行的行头里面的 cmaxxmax 设置为当前命令计数器和当前事务 ID 。这样旧的行就被隐藏起来并且在事务提交之后,vacuum 清理器就可以真正把它们删除掉。

知道了这些,就可以简单的把视图的规则应用到任意命令中。视图和命令没有区别。

35.2.3. PostgreSQL 里视图的强大能力

上面演示了规则系统如何融合到视图定义的初始查询树中去。在第二个例子里,一个简单的对视图的 SELECT 创建了一个四表联合的查询树(unit 以不同的名称用了两次)。

在规则系统里实现视图的好处是,规划器在一个查询树里拥有所有信息:应该扫描哪个表+表之间的关系+视图的资格限制+初始查询的资格(条件)。并且仍然是在最初的查询已经是一个视图的联合的情况下。现在规划器必须决定执行查询的最优路径。规划器拥有越多信息,它的决策就越好。并且 PostgreSQL 里的规则系统的实现保证这些信息是此时能获得的有关该查询的所有信息。

35.2.4. 更新一个视图

如果视图是 INSERT, UPDATE, DELETE 的目标关系会怎样?在完成上面描述的替换之后,就有一个这样的查询树:结果关系指向一个是子查询的范围表记录。这样可不能运行,所以如果重写器看到自己生成这么个东西,它就抛出一个错误。

要修改这个特性,可以定义修改这些命令行为的规则。这是下一节的主题。


后退首页前进
查询树上一级Rules on INSERT, UPDATE, DELETE