| PostgreSQL 9.0.4 中文文档 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. 规则系统 | Fast Forward | Next |
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, -- primary key
sh_avail integer, -- available number of pairs
slcolor text, -- preferred shoelace color
slminlen real, -- minimum shoelace length
slmaxlen real, -- maximum shoelace length
slunit text -- length unit
);
CREATE TABLE shoelace_data (
sl_name text, -- primary key
sl_avail integer, -- available number of pairs
sl_color text, -- shoelace color
sl_len real, -- shoelace length
sl_unit text -- length unit
);
CREATE TABLE unit (
un_name text, -- primary key
un_fact real -- factor to transform to cm
);你可以看到，这些表代表鞋店的数据
试图创建为：
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的范围表记录 (因历史原因，在打印出来的查询树里叫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的目标关系会怎样? 在完成上面描述的替换之后，就有一个这样的查询树：结果关系指向一个是子查询的范围表记录。 这样可不能运行，所以如果重写器看到自己生成这么个东西，它就抛出一个错误。
要修改这个特性，可以定义修改这些命令行为的规则。这是下一节的主题。