CODEWITHSUNDEEP
postgresqltriggersdatabase-partitioning
IamIC
IamIC

Reputation: 18249

PostgreSQL ON BEFORE DELETE trigger not firing on a parent table in an inheritance scenario

I have a parent table and multiple inherited child tables that I'm using as partitions. I'm not using the new method in PG 10 because I manually calculate the routing and require processing on each INSERT, UPDATE and DELETE operation.

I have this trigger:

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

It fires and works correctly on INSERT and UPDATE, but not on DELETE.

I added the following line as line #1 in cms.collections_process() to prove DELETE isn't firing:

  raise exception '(%)', TG_OP;

The row deleted.

The docs state:

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

Any ideas?

Upvotes: 1

Views: 2287

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

UPDATEs and DELETEs on the parent table will affect rows in the child tables (if you don't specify ONLY), but triggers will only be fired by data modifications directed directly against the table with the trigger on it:

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();

Only INSERTs directly on parent file the trigger:

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

The same holds for UPDATE and 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

This seems to be in direct contrast to the sentence in the documentation you quote, or at least the sentence is highly misleading.

I'd say that this is a documentation bug, and you should complain about it.

Upvotes: 2

Related Questions