| PostgreSQL 9.3.1 中文手册 | ||||
|---|---|---|---|---|
| 上一页 | 上一级 | 章 38. 规则系统 | 下一页 | |
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 系统表里的视图的信息与一般表的信息完全一样。所以对于查询分析器来说， 表和视图之间完全没有区别。它们是同样的事物：关系。
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里看到的两个额外的用于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的行为， 那可不是想要的。不过，压缩查询树是重写系统自己不需要关心的优化操作。
有两个查询树的细节在上面的视图规则中没有涉及到。就是命令类型和结果关系。实际上， 视图规则不需要命令类型，但是结果关系可能会影响查询重写的工作方式， 因为如果结果关系是一个视图则需要特别的注意。
一个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 清理器就可以真正把它们删除掉。
知道了这些，就可以简单的把视图的规则应用到任意命令中。规则和命令没有区别。
上面演示了规则系统如何融合视图定义到初始查询树中去。在第二个例子里， 一个简单的对视图的SELECT创建了一个四表联合的查询树 (unit以不同的名称用了两次)。
在规则系统里实现视图的好处是，规划器在一个查询树里拥有所有信息： 应该扫描哪个表+表之间的关系+视图的资格限制+初始查询的资格(条件)。 并且仍然是在最初的查询已经是一个视图的联合的情况下。现在规划器必须决定执行查询的最优路径。 规划器拥有越多信息，它的决策就越好。并且PostgreSQL 里的规则系统的实现保证这些信息是此时能获得的有关该查询的所有信息。
如果视图命名为INSERT, UPDATE,DELETE 的目标关系会怎样?在完成上面描述的替换之后，就有一个这样的查询树： 结果关系指向一个是子查询的范围表记录，这样可不能运行。 在PostgreSQL中有几种方式支持更新一个视图的外观。
如果子查询从一个单一的基本关系选择或者足够简单，重写可以自动的用底层的基本关系替代子查询， 所以INSERT，UPDATE 或 DELETE 以适当的方式应用于基本关系。视图因为"足够简单"而被称为可自动更新的。 有关这种可以自动更新的视图的更详细的信息请参阅CREATE VIEW。
或者，操作可能通过一个用户提供的在视图上的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触发器， 并且重写不能作为一个在底层基本关系上的更新自动重写查询， 那么将抛出一个错误，因为执行器不能像这样的更新视图。