PostgreSQL 8.1 中文文档
PrevFast BackwardChapter 34. 规则系统Fast ForwardNext

34.3. 在 INSERT，UPDATE，和DELETE 上的规则

定义在 INSERT，UPDATE 和 DELETE 的规则与前一章描述的视图规则完全不同。 首先，他们的CREATE RULE命令允许更多：

第二，它们不是就地修改查询树。 它们是创建零个或多个新查询树并且可能把原始的那个仍掉。

34.3.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 关键字），一个或多个动作。 一个或多个动作。为简单起见， 我们先看一眼一个只有一个动作（action）的规则。 这个规则可以有一个条件或没有并且它可以是 INSTEAD 或 ALSO （缺省）。

何为规则条件？它是一个限制条件，告诉规则动作（action）什么时候要做， 什么时候不用做。这个资格（条件）可以只引用 NEW 和/或 OLD 伪关系， 它们基本上是代表以对象形式给出的基本关系（但是有着特殊含义）。

所以，对这个单动作（action）的规则生成查询树，有下面四种情况。

没有条件，也没有 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 的引用都用结果关系的范围表的引用替换。

在系统完成更新规则的附加之后，它再附加视图规则到生成的查询树上。 视图无法插入新的更新动作，所以没有必要向视图重写的输出附加更新规则。

34.3.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';

第四步把 NEWNEW 引用替换为从原始查询树的目标列来的或从结果关系来的相匹配的变量引用： 

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 条件的。

34.3.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;

现在有一包鞋带到达商店，而且这是一大笔到货。 但是我们不想每次都手工更新 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， 这个视图带有一个总共使用了四个嵌套/连接的视图的子查询条件， 这四个视图之一本身有一个拥有对一个视图的子查询条件， 该条件计算使用的视图的列；最后重写成了一个查询树， 该查询树从一个真正的表里面把需要删除的数据删除。

我想在现实世界里只有很少的机会需要上面的这样的构造。 但这些东西能运转肯定让你舒服。

PrevHomeNext
视图和规则系统Up规则和权限