问题报告 纠错本页面
PostgreSQL 9.3.1 中文手册
PrevUpChapter 38. 规则系统Next

38.4. 在 INSERT, UPDATE, 和 DELETE上的规则

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

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

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，这个视图带有一个总共使用了四个嵌套/连接的视图的子查询条件， 这四个视图之一本身有一个拥有对一个视图的子查询条件，该条件计算使用的视图的列； 最后重写成了一个查询树，该查询树从一个真正的表里面把需要删除的数据删除。

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

PrevHomeNext
物化视图Up规则和权限