一条 UPDATE 出两条查询树:PostgreSQL 规则里 INSERT 先执行,UPDATE/DELETE 后执行
文档对应 PostgreSQL 19 Chapter 39 / 39.4 / 39.7:
- 规则系统:
- 39.4 INSERT/UPDATE/DELETE 上的规则:
- 39.7 规则与触发器:
先看一个具体现象。shoelace_data 上挂一条日志规则:
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';
重写阶段输出的不是一条语句,而是两棵查询树:一棵 INSERT,条件里带着 WHERE 6 <> shoelace_data.sl_avail AND sl_name = 'sl7';另一棵是原 UPDATE。反直觉的地方在于顺序——文档明确说,如果先执行 UPDATE,所有行都已被置为 0,日志 INSERT 就找不到 0 <> sl_avail 的行,所以原 UPDATE 必须放在最后执行。
规则在解析器与规划器之间
规则系统(更准确地说是查询重写规则系统)和存储过程、触发器是两套机制。它的位置在解析器与规划器之间:输入是解析器输出的查询树,加上用户定义的重写规则(规则本身也是带额外信息的查询树);输出是零个或多个查询树。
规则动作以查询树形式存放在系统目录 pg_rewrite 中。存的是模板,使用前必须把 NEW / OLD 的 range table 引用替换掉。
视图就是一条 _RETURN 规则:
CREATE VIEW myview AS SELECT * FROM mytab;
内部等价于 CREATE TABLE myview(同列)+ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab;。
语法与默认值
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ]
{ NOTHING | command | ( command ; command ... ) }
| 事件 | 动作数 | ALSO / INSTEAD | NEW/OLD | 规则条件 | 对查询树的影响 |
|---|---|---|---|---|---|
ON SELECT | 只能 1 个 | 必须是无条件 INSTEAD SELECT | 不适用 | 不允许 | 就地修改查询树,不新建树,语义上等同视图 |
ON INSERT/UPDATE/DELETE | 0 个或多个 | INSTEAD 或 ALSO,ALSO 是默认 | 可用 | 可带 qualification | 创建 0 到多棵新查询树,可以丢弃原树 |
单动作规则按文档分三种情况:
- 无条件
ALSO/INSTEAD:动作树 + 原查询树的条件。 - 有条件
ALSO:动作树 + 规则条件 + 原查询条件。 - 有条件
INSTEAD:动作树 + 规则条件 + 原查询条件,另外把原查询树加上「反规则条件」也加入列表。
如果是 ALSO,未修改的原查询树也会加入列表;只有带条件的 INSTEAD 会额外加入原树。所以单动作规则最终输出 1 或 2 棵树。
执行顺序:INSERT 先,UPDATE/DELETE 后
这是规则里最容易踩错的一处,文档写得直接:
- 对
ON INSERT规则,原查询(若未被INSTEAD取代)在规则动作之前执行,使动作能看到被插入的行。 - 对
ON UPDATE与ON DELETE规则,原查询在规则动作之后执行,确保动作能看到将被更新/删除的行;否则动作可能找不到匹配行。
log_shoelace 就是第二种。日志 INSERT 必须先跑,才能读到更新前的 sl_avail。
NEW 找不到目标项时规则条件恒假
NEW 的替换规则:在原始查询的目标列表中查找对应项,找到就用该表达式替换。找不到时——比如原 UPDATE 只改 sl_color,目标列表里没有 sl_avail——NEW.sl_avail 会变成 shoelace_data.sl_avail。于是规则条件恒假,不写日志。
任何 OLD 引用都替换为结果关系的 range table 引用。
递归与视图规则的收尾
规则动作生成的查询树会被再次投入重写系统,可能再应用更多规则。因此规则的动作必须与规则自身有不同的命令类型,或不同的结果关系;否则递归展开会无限循环。这种递归展开会被检测到,并当作错误报告。
规则动作应用完后,系统再对生成的查询树应用视图规则。视图不能插入新的更新动作,所以视图重写的输出不需要再应用更新规则。
互斥与失效点
不支持的查询结构。 原查询包含 WITH 子句,或 UPDATE 的 SET 列表中含多赋值子 SELECT 时,这类规则根本无法支持。把这些结构复制进规则查询会导致子查询被多次计算,违背作者意图。
volatile 函数。 原查询含 volatile 函数时,规则容易给出意外结果:执行规则过程中 volatile 函数可能被执行超过预期次数。
用规则做校验是错的。 带条件的规则可以在列值不在另一张表时把命令重写成 NOTHING,但这样数据是被静默丢弃的。需要校验并报错(如外键)必须用触发器。
保护视图。 带 DO INSTEAD NOTHING 的规则会让输出查询树列表为空,整条语句什么都不做:
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
DO INSTEAD NOTHING;
UPDATE / DELETE 同理。
RETURNING 里的 OLD/NEW 不是结果行。 视图上支持 RETURNING 需要在规则里写 RETURNING 子句计算视图行。规则 RETURNING 里的 OLD / NEW 指额外加入 range table 的伪关系,而不是结果关系的行。例如写 old.sl_name 会总是返回旧名,无论外层 RETURNING 写的是 OLD 还是 NEW。应改为引用结果关系列,如 shoelace_data.sl_name。
多 INSTEAD 规则串联。 shoelace_ok_ins → shoelace_upd → _RETURN → log_shoelace 这条链最终把一条 INSERT ... SELECT 规约成两条查询树。副作用是 range table 中出现重复的 shoelace_data,规划器不合并,导致多一次不必要的顺序扫描。
规则与触发器的分工。 触发器每影响一行触发一次;规则是修改查询或生成一条额外查询。一条语句影响很多行时,发一条额外命令的规则通常比逐行触发、反复判定的触发器快。规则无法实现某些约束,尤其外键。规则适合更新视图,但同样的更新规则用视图上的 INSTEAD OF 触发器写通常更容易,复杂逻辑尤其如此。结论:规则只有在动作产生大而限定不良的 join(规划器失效)时才显著慢于触发器。
删除规则示例:
CREATE RULE computer_del AS ON DELETE TO computer
DO DELETE FROM software WHERE hostname = OLD.hostname;
对比 DELETE FROM computer WHERE hostname ~ '^old':规则生成的语句能用两个索引扫描;触发器会对 2000 行各执行一次,各自再索引扫描 software。
与站内 #8708(MySQL Rewriter 的加载与匹配)不同,这篇讲 PostgreSQL 规则的查询树替换与执行顺序;与 #8581(pg_locks 自连接)无关。