41.4. 关于`INSERT`、`UPDATE`和`DELETE`的规则#

41.4.1. 更新规则的工作原理
41.4.2. 与视图的协作

在INSERT、UPDATE和DELETE上定义的规则与前几节中描述的视图规则有很大不同。首先，它们的CREATE RULE命令允许更多

它们可以没有动作。
它们可以有多个动作。
它们可以是 INSTEAD 或 ALSO（默认）。
伪关系 NEW 和 OLD 变得有用。
它们可以有规则限定。

其次，它们不会就地修改查询树。相反，它们创建零个或多个新的查询树，并且可以丢弃原始查询树。

注意

在许多情况下，可以使用INSERT/UPDATE/DELETE上的规则执行的任务最好使用触发器来完成。触发器的表示法稍微复杂一些，但它们的语义更容易理解。当原始查询包含不稳定函数时，规则往往会产生令人惊讶的结果：在执行规则的过程中，不稳定函数可能会被执行多次，超出预期。

此外，还有一些情况不受这些类型的规则支持，尤其是原始查询中的WITH子句和UPDATE查询的SET列表中的多重赋值子SELECT。这是因为将这些结构复制到规则查询中会导致对子查询进行多次评估，这与查询作者的明确意图相违背。

41.4.1. 更新规则的工作原理#

记住语法。

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

在以下内容中，更新规则表示在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系统目录的动作中找到的查询树只是模板。由于它们可以引用NEW和OLD的范围表条目，因此在使用它们之前必须进行一些替换。对于任何对NEW的引用，都会在原始查询的目标列表中搜索相应条目。如果找到，则该条目的表达式将替换引用。否则，NEW的含义与OLD相同（对于UPDATE）或被空值替换（对于INSERT）。对OLD的任何引用都将替换为对结果关系的范围表条目的引用。

系统完成应用更新规则后，它会将视图规则应用到生成的查询树。视图无法插入新的更新操作，因此无需将更新规则应用到视图重写的输出。

41.4.1.1. 分步执行第一个规则#

假设我们要跟踪shoelace_data关系中sl_avail列的更改。因此，我们设置了一个日志表和一个规则，当对shoelace_data执行UPDATE时，该规则有条件地写入日志条目。

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

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

有一个规则log_shoelace，它在ON UPDATE中，规则限定表达式为

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规则，因此规则系统必须返回两个查询树：修改后的规则操作和原始查询树。在步骤 1 中，原始查询的范围表被合并到规则的操作查询树中。这将导致

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;

在步骤 2 中，向其添加规则限定，因此结果集被限制为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的相同功能。）

在步骤 3 中，添加原始查询树的限定，进一步将结果集限制为仅原始查询会触及的行

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

步骤 4 用原始查询树的目标列表条目或结果关系的匹配变量引用替换对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';

步骤 5 将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的行。

41.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)

现在将运到的鞋带移入

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，其中子查询限定符总共使用 4 个嵌套/联接视图，其中一个视图本身具有包含视图的子查询限定符，并且使用了计算的视图列，这些操作被重写为一个从真实表中删除请求数据的单个查询树。

现实世界中可能只有少数情况下需要这种结构。但它让你觉得它有效，让你感到安心。

41.4. 关于INSERT、UPDATE和DELETE的规则# ​

注意 ​

41.4.1. 更新规则的工作原理# ​

41.4.1.1. 分步执行第一个规则# ​

41.4.2. 与视图的协作# ​

41.4. 关于`INSERT`、`UPDATE`和`DELETE`的规则#

注意

41.4.1. 更新规则的工作原理#

41.4.1.1. 分步执行第一个规则#

41.4.2. 与视图的协作#