PostgreSQL ON BEFORE DELETE 触发器不在继承场景中的父表上触发

Question

我有一个父表和多个用作分区的继承子表。我没有使用 PG 10 中的新方法，因为我手动计算路由并且需要对每个 INSERT、UPDATE 和 DELETE 操作进行处理。

我有这个触发器:

CREATE TRIGGER tg_collections_all
  BEFORE UPDATE OR INSERT OR DELETE
  ON cms.collections
  FOR EACH ROW
  EXECUTE PROCEDURE cms.collections_process();

它在 INSERT 和 UPDATE 上触发并正常工作，但在 DELETE 上不正确。

我在 cms.collections_process() 中添加了以下行作为第 1 行，以证明 DELETE 没有触发:

  raise exception '(%)', TG_OP;

删除的行。

docs状态:

In contrast, row-level triggers are fired for all affected partitions or child tables.

有什么想法吗？

Answer 1

父表上的

UPDATE 和 DELETE 将影响子表中的行(如果您未指定 ONLY)，但触发器只会被直接针对带有触发器的表的数据修改触发:

CREATE TABLE parent(id integer, val text);
CREATE TABLE child() INHERITS (parent);

CREATE OR REPLACE FUNCTION inh_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   RAISE NOTICE 'Called by %', TG_OP;
   RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;$$;

CREATE TRIGGER inh_trigger BEFORE INSERT OR UPDATE OR DELETE ON parent
   FOR EACH ROW EXECUTE PROCEDURE inh_trigger();

只有 INSERT 直接在 parent 文件上触发:

test=> INSERT INTO parent VALUES (1, 'one');
NOTICE:  Called by INSERT
INSERT 0 1
test=> INSERT INTO child VALUES (2, 'two');
INSERT 0 1

同样适用于 UPDATE 和 DELETE:

test=> UPDATE parent SET val = 'changed' WHERE id = 1;
NOTICE:  Called by UPDATE
UPDATE 1
test=> UPDATE parent SET val = 'changed' WHERE id = 2;
UPDATE 1
test=> DELETE FROM parent WHERE id = 1;
NOTICE:  Called by DELETE
DELETE 1
test=> DELETE FROM parent WHERE id = 2;
DELETE 1

这似乎与您引用的文档中的句子形成鲜明对比，或者至少该句子具有很强的误导性。

我会说这是一个文档错误，你应该提示它。