Postgres 11中带JOIN的INSERT语句未触发INSERT规则问题排查

问题根源 #

PostgreSQL的规则系统基于查询重写实现，而非行级触发机制。当INSERT语句使用包含LEFT OUTER JOIN + WHERE的反连接逻辑作为数据源时，Postgres查询优化器会将这类反连接查询等价转换为NOT EXISTS形式的子查询。规则系统在处理这种优化后的查询结构时，无法正确将DO ALSO的插入逻辑关联到反连接生成的每一行数据上，导致history表的插入操作完全不触发。

而仅带简单WHERE条件的查询不会触发复杂的等价转换，规则系统能正常识别并将原语句重写为包含两个INSERT的组合语句，因此可以正常生效。

解决方案 #

将INSERT规则替换为行级AFTER INSERT触发器，触发器针对每一行插入操作触发，不受查询结构和优化器转换的影响，无论插入数据源是简单查询还是反连接查询，都会为每一行插入记录到history表。

示例触发器代码：

CREATE OR REPLACE FUNCTION log_machine_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(machine_name, stamp, ...)
  VALUES(NEW.machine_name, now(), ...);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER machine_insert_trigger
AFTER INSERT ON machines
FOR EACH ROW
EXECUTE FUNCTION log_machine_insert();

补充说明 #

• 规则适合语句级批量操作，但对复杂查询的兼容性较差；触发器在需要行级精准触发的场景下更可靠。
• Postgres 11及更高版本中，触发器性能足够优秀，不会比规则有明显性能损耗，反而在复杂场景下稳定性更强。

内容的提问来源于stack exchange，提问作者zoran