# 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`触发器， 并且重写不能作为一个在底层基本关系上的更新自动重写查询， 那么将抛出一个错误，因为执行器不能像这样的更新视图。