38.4. 在 INSERT, UPDATE, 和 DELETE上的规则 · PostgreSQL 中文文档 9.3

# 38.4\. 在 `INSERT`, `UPDATE`, 和 `DELETE`上的规则定义在`INSERT`, `UPDATE`, `DELETE` 上的规则与前一章描述的视图规则完全不同。首先，他们的`CREATE RULE`命令允许更多： * 它们可以没有动作。 * 它们可以有多个动作。 * 他们可以是`INSTEAD`或`ALSO`(缺省)。 * 伪关系`NEW`和`OLD`变得有用了。 * 它们可以有规则资格条件。第二，它们不是就地修改查询树，而是创建零个或多个新查询树并且可能把原始的那个扔掉。 ## 38.4.1\. 更新规则是如何运转的把下面语法： ``` CREATE [ OR REPLACE ] RULE _name_ AS ON _event_ TO _table_ [ WHERE _condition_ ] DO [ ALSO | INSTEAD ] { NOTHING | _command_ | ( _command_ ; _command_ ... ) } ``` 牢牢记住。在随后的内容里，_update rules_(更新规则)意思是定义在 `INSERT`, `UPDATE`, 或 `DELETE`上的规则。如果查询树的结果关系和命令类型与`CREATE RULE`命令里给出的对象和事件一样的话，规则系统就把更新规则应用上去。对于更新规则，规则系统创建一个查询树列表。一开始查询树是空的，这里可以有零个(`NOTHING`关键字)、一个、或多个动作。为简单起见，先看一个只有一个动作的规则。这个规则可以有零个或一个条件并且它可以是 `INSTEAD`或`ALSO`(缺省)。何为规则条件?它是一个限制条件，告诉规则动作什么时候要做，什么时候不要做。这个条件可以只引用`NEW`和/或`OLD`伪关系，它们基本上是代表以对象形式给出的基本关系(但是有着特殊含义)。所以，对这个单动作的规则生成查询树，有下面三种情况。没有条件，也没有`ALSO`或`INSTEAD` 来自规则动作的查询树，附加了原始查询树的条件。给出了条件，有`ALSO` 来自规则动作的带有规则条件的查询树并且附加了原始查询树的条件。给出了条件，有`INSTEAD` 来自规则动作带有规则条件的查询树以及原始查询树的条件；以及附加了相反规则条件的原始查询树。最后，如果规则是`ALSO`，那么最初未修改的查询树被加入到列表。因为只有合格的`INSTEAD`规则已经在初始的查询树里面，所以对于单动作规则最终得到一个或者两个查询树。对于`ON INSERT`规则，原来的查询(如果没有被`INSTEAD`取代) 是在任何规则增加的动作之前完成的。这样就允许动作看到插入的行。但是对`ON UPDATE` 和`ON DELETE`规则，原来的查询是在规则增加的动作之后完成的。这样就确保动作可以看到将要更新或者将要删除的行；否则，动作可能什么也不做，因为它们发现没有符合它们要求的行。从规则动作生成的查询树被再次送到重写系统，并且可能附加更多的规则，结果是更多的或更少的查询树。所以规则动作必须是另一个命令类型或者和规则所在的关系不同的另一个结果关系。否则这样的递归过程就会没完没了(规则的递规展开会被检测到，并当作一个错误报告)。在`pg_rewrite`系统表中 action 里的查询树只是模板。因为他们可以引用范围表的`NEW`和`OLD`，在使用它们之前必须做一些调整。对于任何对`NEW`的引用，都要先在初始查询的目标列中搜索对应的条目。如果找到，把该条目表达式放到引用里。否则`NEW`和`OLD`的含义一样(对于`UPDATE`) 或者被 NULL 替代(对于`INSERT`)。任何对`OLD` 的引用都用结果关系的范围表的引用替换。在系统完成更新规则的附加之后，它再附加视图规则到生成的查询树上。视图无法插入新的更新动作，所以没有必要向视图重写的输出附加更新规则。 ### 38.4.1.1\. 循序渐进的第一个规则假设希望跟踪`shoelace_data`关系中的`sl_avail`字段。所以设置一个日志表和一条规则，这条规则每次在用`UPDATE`更新 `shoelace_data`表时都要往数据库里写一条记录。 ``` CREATE TABLE shoelace_log ( sl_name text, -- 鞋带变化了 sl_avail integer, -- 新的可用数值 log_who text, -- 谁干的 log_when timestamp -- 什么时候 ); CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE NEW.sl_avail <> OLD.sl_avail DO INSERT INTO shoelace_log VALUES ( NEW.sl_name, NEW.sl_avail, current_user, current_timestamp ); ``` 现在有人键入： ``` UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; ``` 然后看看日志表： ``` SELECT * FROM shoelace_log; sl_name | sl_avail | log_who | log_when ---------+----------+---------+---------------------------------- sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST (1 row) ``` 这是想要的。后端发生的事情如下。分析器创建查询树： ``` UPDATE shoelace_data SET sl_avail = 6 FROM shoelace_data shoelace_data WHERE shoelace_data.sl_name = 'sl7'; ``` 这里是一个带有条件表达式的`ON UPDATE`规则`log_shoelace`： ``` NEW.sl_avail <> OLD.sl_avail ``` 和动作： ``` INSERT INTO shoelace_log VALUES ( new.sl_name, new.sl_avail, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old; ``` 这个输出看起来有点奇怪，因为你不能写`INSERT ... VALUES ... FROM`。这里的`FROM`子句只是表示查询树里有用于`new`和`old` 的范围表记录。这些东西的存在是因为这样一来它们就可以被`INSERT` 命令的查询树里的变量引用。该规则是一个有条件的`ALSO`规则，所以规则系统必须返回两个查询树：更改过的规则动作和原始查询树。在第一步里，原始查询的范围表集成到规则动作查询树里。生成： ``` INSERT INTO shoelace_log VALUES ( new.sl_name, new.sl_avail, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old, **shoelace_data shoelace_data**; ``` 第二步把规则条件增加进去，所以结果集限制为`sl_avail`改变了的行： ``` INSERT INTO shoelace_log VALUES ( new.sl_name, new.sl_avail, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old, shoelace_data shoelace_data **WHERE new.sl_avail <> old.sl_avail**; ``` 这个东西看起来更奇怪，因为`INSERT ... VALUES`也没有`WHERE`子句，不过规划器和执行器对此并不在意。它们毕竟还要为`INSERT ... SELECT`支持这种功能。第三步把原始查询树的条件加进去，把结果集进一步限制成只有被初始查询树改变的行： ``` INSERT INTO shoelace_log VALUES ( new.sl_name, new.sl_avail, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old, shoelace_data shoelace_data WHERE new.sl_avail <> old.sl_avail **AND shoelace_data.sl_name = 'sl7'**; ``` 第四步把`NEW`引用替换为从原始查询树来的目标列或从结果关系来的相匹配的变量引用： ``` INSERT INTO shoelace_log VALUES ( **shoelace_data.sl_name**, **6**, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old, shoelace_data shoelace_data WHERE **6** <> old.sl_avail AND shoelace_data.sl_name = 'sl7'; ``` 第五步用结果关系引用把`OLD`引用替换掉： ``` INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) FROM shoelace_data new, shoelace_data old, shoelace_data shoelace_data WHERE 6 <> **shoelace_data.sl_avail** AND shoelace_data.sl_name = 'sl7'; ``` 这就成了。因为规则`ALSO`还输出原始查询树。简而言之，从规则系统输出的是一个两个查询树的列表，与下面语句相同： ``` INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) FROM shoelace_data WHERE 6 <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; ``` 这就是执行的顺序以及规则要做的事情。做的替换和追加的条件用于确保如果原始的查询是下面这样： ``` UPDATE shoelace_data SET sl_color = 'green' WHERE sl_name = 'sl7'; ``` 就不会有日期记录写到表里。因为这回原始查询树不包含有关`sl_avail` 的目标列表，`NEW.sl_avail`将被`shoelace_data.sl_avail`代替，所以，规则生成的额外命令是： ``` INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, **shoelace_data.sl_avail**, current_user, current_timestamp ) FROM shoelace_data WHERE **shoelace_data.sl_avail** <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; ``` 并且条件将永远不可能是真值。如果最初的查询修改多个行，它也能运行。所以如果写出下面命令： ``` UPDATE shoelace_data SET sl_avail = 0 WHERE sl_color = 'black'; ``` 实际上有四行被更新(`sl1`, `sl2`, `sl3`, 和`sl4`)。但`sl3`已经是`sl_avail = 0`。这回，原始的查询树条件已经不一样了，结果是规则生成下面的额外查询树： ``` INSERT INTO shoelace_log SELECT shoelace_data.sl_name, 0, current_user, current_timestamp FROM shoelace_data WHERE 0 <> shoelace_data.sl_avail AND **shoelace_data.sl_color = 'black'**; ``` 这个查询树将肯定插入三个新的日志记录。这也是完全正确的。到这里就明白为什么原始查询树最后执行非常重要。如果`UPDATE`将先被执行，所有的行都已经设为零，所以记日志的`INSERT`将不能找到任何符合 `0 <> shoelace_data.sl_avail`条件的行。 ## 38.4.2\. 与视图合作一个保护视图关系，使其避免有人可以在其中`INSERT`, `UPDATE`, `DELETE`的简单方法是让那些查询树被丢弃。创建下面规则： ``` CREATE RULE shoe_ins_protect AS ON INSERT TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING; ``` 如果现在任何人试图对视图关系`shoe`做上面的任何操作，规则系统将应用这些规则。因为这些规则没有动作而且是`INSTEAD`，结果是生成的查询树将是空的并且整个查询将变得空空如也，因为经过规则系统处理后没有什么东西剩下来用于优化或执行了。一个更复杂的使用规则系统的方法是用规则系统创建一个重写查询树的规则，使查询树对真实的表进行正确的操作。要在视图`shoelace`上做这个工作，创建下面规则： ``` CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data VALUES ( NEW.sl_name, NEW.sl_avail, NEW.sl_color, NEW.sl_len, NEW.sl_unit ); CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = NEW.sl_name, sl_avail = NEW.sl_avail, sl_color = NEW.sl_color, sl_len = NEW.sl_len, sl_unit = NEW.sl_unit WHERE sl_name = OLD.sl_name; CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE sl_name = OLD.sl_name; ``` 如果你打算在视图上支持`RETURNING`查询，就要让规则包含`RETURNING` 计算视图行数的子句。这对于基于单个表的视图来说通常非常琐碎，但是连接诸如 `shoelace`之类的视图很单调乏味。一个插入情况的例子如下： ``` CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data VALUES ( NEW.sl_name, NEW.sl_avail, NEW.sl_color, NEW.sl_len, NEW.sl_unit ) RETURNING shoelace_data.*, (SELECT shoelace_data.sl_len * u.un_fact FROM unit u WHERE shoelace_data.sl_unit = u.un_name); ``` 注意，这个规则同时支持该视图上的`INSERT`和`INSERT RETURNING`查询， `INSERT`将简单的忽略`RETURNING`子句。假设现在有一包鞋带到达商店，还有一个大的部件列表。但是不想每次都手工更新 `shoelace`视图。取而代之的是创建了两个小表：一个是可以从到货清单中插入东西，另一个是一个特殊的技巧。创建这些的命令如下： ``` CREATE TABLE shoelace_arrive ( arr_name text, arr_quant integer ); CREATE TABLE shoelace_ok ( ok_name text, ok_quant integer ); CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = sl_avail + NEW.ok_quant WHERE sl_name = NEW.ok_name; ``` 现在你可以用来自部件列表的数据填充表`shoelace_arrive`了： ``` SELECT * FROM shoelace_arrive; arr_name | arr_quant ----------+----------- sl3 | 10 sl6 | 20 sl8 | 20 (3 rows) ``` 让我们迅速地看一眼当前的数据， ``` 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 | 6 | 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) ``` 把到货鞋带移到(shoelace_ok)中： ``` INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive; ``` 然后检查结果： ``` SELECT * FROM shoelace ORDER BY sl_name; 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 | 6 | brown | 60 | cm | 60 sl4 | 8 | black | 40 | inch | 101.6 sl3 | 10 | black | 35 | inch | 88.9 sl8 | 21 | brown | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 (8 rows) SELECT * FROM shoelace_log; sl_name | sl_avail | log_who| log_when ---------+----------+--------+---------------------------------- sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST (4 rows) ``` 从`INSERT ... SELECT`语句到这个结果经过了长长的一段过程。而且对查询树转化的描述将是本文档的最后。首先是生成分析器输出： ``` INSERT INTO shoelace_ok SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok; ``` 现在应用第一条规则`shoelace_ok_ins`把它转换成： ``` UPDATE shoelace SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace WHERE shoelace.sl_name = shoelace_arrive.arr_name; ``` 并且把原始的对`shoelace_ok`的`INSERT`丢弃掉。这样重写后的查询再次传入规则系统并且第二次应用了规则`shoelace_upd`生成： ``` UPDATE shoelace_data SET sl_name = shoelace.sl_name, sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant, sl_color = shoelace.sl_color, sl_len = shoelace.sl_len, sl_unit = shoelace.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data WHERE shoelace.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = shoelace.sl_name; ``` 同样这是一个`INSTEAD`规则并且前一个查询树被丢弃掉。注意这个查询仍然是使用视图`shoelace`，但是规则系统还没有完成这一步，所以它继续在这上面应用规则`_RETURN`，然后得到： ``` UPDATE shoelace_data SET sl_name = s.sl_name, sl_avail = s.sl_avail + shoelace_arrive.arr_quant, sl_color = s.sl_color, sl_len = s.sl_len, sl_unit = s.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data, shoelace old, shoelace new, shoelace_data s, unit u WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name; ``` 最后，应用规则`log_shoelace`，生成额外的查询树： ``` INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok old, shoelace_ok new, shoelace shoelace, shoelace old, shoelace new, shoelace_data shoelace_data, shoelace old, shoelace new, shoelace_data s, unit u, shoelace_data old, shoelace_data new shoelace_log shoelace_log WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail; ``` 这样，在规则系统用完所有的规则后返回生成的查询树。所以最终得到两个等效于下面SQL语句的查询树： ``` INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail; UPDATE shoelace_data SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.sl_name AND shoelace_data.sl_name = s.sl_name; ``` 结果是从一个关系来的数据插入到另一个中，到了第三个中变成更新，在到第四个中变成更新加上记日志，最后在第五个规则中缩减为两个查询。有一个小细节有点让人难受。看看生成的两个查询，会发现`shoelace_data` 关系在范围表中出现了两次而实际上绝对可以缩为一次。因为规划器不处理这些，所以对规则系统输出的`INSERT`的执行规划会是 ``` Nested Loop -> Merge Join -> Seq Scan -> Sort -> Seq Scan on s -> Seq Scan -> Sort -> Seq Scan on shoelace_arrive -> Seq Scan on shoelace_data ``` 在省略多余的范围表后的结果将是 ``` Merge Join -> Seq Scan -> Sort -> Seq Scan on s -> Seq Scan -> Sort -> Seq Scan on shoelace_arrive ``` 这也会在日志关系中生成完全一样的记录。因此，规则系统导致对表`shoelace_data` 的一次多余的扫描，而且同样多余的扫描会在`UPDATE`里也一样多做一次。不过要想把这些不足去掉是一样太困难的活了。最后对PostgreSQL规则系统及其功能做一个演示。假设你向你的数据库中添加一些比较罕见的鞋带： ``` INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0); INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); ``` 建立一个视图检查哪种`shoelace`记录在颜色上和任何鞋子都不相配。用于这个的视图是： ``` CREATE VIEW shoelace_mismatch AS SELECT * FROM shoelace WHERE NOT EXISTS (SELECT shoename FROM shoe WHERE slcolor = sl_color); ``` 它的输出是： ``` SELECT * FROM shoelace_mismatch; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ---------+----------+----------+--------+---------+----------- sl9 | 0 | pink | 35 | inch | 88.9 sl10 | 1000 | magenta | 40 | inch | 101.6 ``` 现在想这样设置：没有库存的不匹配的鞋带都从数据库中删除。为了让这事对 PostgreSQL有点难度，不直接删除它们。取而代之的是再创建一个视图: ``` CREATE VIEW shoelace_can_delete AS SELECT * FROM shoelace_mismatch WHERE sl_avail = 0; ``` 然后用下面方法做： ``` DELETE FROM shoelace WHERE EXISTS (SELECT * FROM shoelace_can_delete WHERE sl_name = shoelace.sl_name); ``` _所以à_: ``` 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 | 6 | brown | 60 | cm | 60 sl4 | 8 | black | 40 | inch | 101.6 sl3 | 10 | black | 35 | inch | 88.9 sl8 | 21 | brown | 40 | inch | 101.6 sl10 | 1000 | magenta | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 (9 rows) ``` 对一个视图的`DELETE`，这个视图带有一个总共使用了四个嵌套/连接的视图的子查询条件，这四个视图之一本身有一个拥有对一个视图的子查询条件，该条件计算使用的视图的列；最后重写成了一个查询树，该查询树从一个真正的表里面把需要删除的数据删除。我想在现实世界里只有很少的机会需要上面的这样的构造。但这些东西能运转肯定让你舒服。