rules-triggers.zh.md

## 41.7. Rules Versus Triggers

[](<>)[](<>)

Many things that can be done using triggers can also be implemented using the PostgreSQL rule system. One of the things that cannot be implemented by rules are some kinds of constraints, especially foreign keys. It is possible to place a qualified rule that rewrites a command to`NOTHING`if the value of a column does not appear in another table. But then the data is silently thrown away and that's not a good idea. If checks for valid values are required, and in the case of an invalid value an error message should be generated, it must be done by a trigger.

In this chapter, we focused on using rules to update views. All of the update rule examples in this chapter can also be implemented using`INSTEAD OF`triggers on the views. Writing such triggers is often easier than writing rules, particularly if complex logic is required to perform the update.

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired once for each affected row. A rule modifies the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must re-determine what to do many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

Here we show an example of how the choice of rules versus triggers plays out in one situation. There are two tables:

```
CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);
```

Both tables have many thousands of rows and the indexes on`hostname`are unique. The rule or trigger should implement a constraint that deletes rows from`software`that reference a deleted computer. The trigger would use this command:

```
DELETE FROM software WHERE hostname = $1;
```

Since the trigger is called for each individual row deleted from`computer`, it can prepare and save the plan for this command and pass the`hostname`value in the parameter. The rule would be written as:

```
CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;
```

Now we look at different types of deletes. In the case of a:

```
DELETE FROM computer WHERE hostname = 'mypc.local.net';
```

the table`computer`is scanned by index (fast), and the command issued by the trigger would also use an index scan (also fast). The extra command from the rule would be:

```
DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;
```

Since there are appropriate indexes set up, the planner will create a plan of

```
Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software
```

So there would be not that much difference in speed between the trigger and the rule implementation.

With the next delete we want to get rid of all the 2000 computers where the`hostname`starts with`old`.有两个可能的命令可以做到这一点。一种是：

```
DELETE FROM computer WHERE hostname >= 'old'
                       AND hostname <  'ole'
```

规则添加的命令将是：

```
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
                       AND software.hostname = computer.hostname;
```

与计划

```
Hash Join
  ->  Seq Scan on software
  ->  Hash
    ->  Index Scan using comp_hostidx on computer
```

另一个可能的命令是：

```
DELETE FROM computer WHERE hostname ~ '^old';
```

这导致规则添加的命令的执行计划如下：

```
Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software
```

这表明，规划者没有意识到`主机名`在`计算机`也可以用于索引扫描`软件`当有多个限定表达式与`和`，这就是它在命令的正则表达式版本中所做的。触发器将为必须删除的 2000 台旧计算机中的每台调用一次，这将导致一次索引扫描`计算机`和 2000 次索引扫描`软件`.规则实现将使用两个使用索引的命令来完成。这取决于桌子的整体大小`软件`在顺序扫描情况下规则是否仍然更快。通过 SPI 管理器从触发器执行 2000 次命令需要一些时间，即使所有索引块很快都会在缓存中。

我们查看的最后一个命令是：

```
DELETE FROM computer WHERE manufacturer = 'bim';
```

同样，这可能会导致许多行被删除`计算机`.所以触发器将再次通过执行器运行许多命令。规则生成的命令将是：

```
DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;
```

该命令的计划将再次成为两次索引扫描的嵌套循环，仅使用不同的索引`计算机`：

```
Nestloop
  ->  Index Scan using comp_manufidx on computer
  ->  Index Scan using soft_hostidx on software
```

在任何这些情况下，来自规则系统的额外命令将或多或少独立于命令中受影响的行数。

总结是，如果规则的操作导致大型且不合格的联接，则规则只会比触发器慢得多，这是规划器失败的情况。