This is a tricky problem. But it can be done with per-column triggers and conditional trigger execution introduced in PostgreSQL 9.0.
You need an "updated" flag per row for this solution. Use a boolean column in the same table for simplicity. But it could be in another table or even a temporary table per transaction.
The expensive payload is executed once per row where the counter is updated (once or multiple time).
This should also perform well, because ...
- ... it avoids multiple calls of triggers at the root (scales well)
- ... does not change additional rows (minimize table bloat)
- ... does not need expensive exception handling.
Consider the following
Demo
Tested in PostgreSQL 9.1 with a separate schema x as test environment.
Tables and dummy rows
-- DROP SCHEMA x;
CREATE SCHEMA x;
CREATE TABLE x.tbl (
id int
,counter int
,trig_exec_count integer -- for monitoring payload execution.
,updated bool);
Insert two rows to demonstrate it works with multiple rows:
INSERT INTO x.tbl VALUES
(1, 0, 0, NULL)
,(2, 0, 0, NULL);
Trigger functions and Triggers
1.) Execute expensive payload
CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_1()
RETURNS trigger AS
$BODY$
BEGIN
-- PERFORM some_expensive_procedure(NEW.id);
-- Update trig_exec_count to count execution of expensive payload.
-- Could be in another table, for simplicity, I use the same:
UPDATE x.tbl t
SET trig_exec_count = trig_exec_count + 1
WHERE t.id = NEW.id;
RETURN NULL; -- RETURN value of AFTER trigger is ignored anyway
END;
$BODY$ LANGUAGE plpgsql;
2.) Flag row as updated.
CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_2()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE x.tbl
SET updated = TRUE
WHERE id = NEW.id;
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;
3.) Reset "updated" flag.
CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_3()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE x.tbl
SET updated = NULL
WHERE id = NEW.id;
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;
Trigger names are relevant! Called for the same event they are executed in alphabetical order.
1.) Payload, only if not "updated" yet:
CREATE CONSTRAINT TRIGGER upaft_counter_change_1
AFTER UPDATE OF counter ON x.tbl
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN (NEW.updated IS NULL)
EXECUTE PROCEDURE x.trg_upaft_counter_change_1();
2.) Flag row as updated, only if not "updated" yet:
CREATE TRIGGER upaft_counter_change_2 -- not deferred!
AFTER UPDATE OF counter ON x.tbl
FOR EACH ROW
WHEN (NEW.updated IS NULL)
EXECUTE PROCEDURE x.trg_upaft_counter_change_2();
3.) Reset Flag. No endless loop because of trigger condition.
CREATE CONSTRAINT TRIGGER upaft_counter_change_3
AFTER UPDATE OF updated ON x.tbl
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN (NEW.updated) --
EXECUTE PROCEDURE x.trg_upaft_counter_change_3();
Test
Run UPDATE & SELECT separately to see the deferred effect. If executed together (in one transaction) the SELECT will show the new tbl.counter but the old tbl2.trig_exec_count.
UPDATE x.tbl SET counter = counter + 1;
SELECT * FROM x.tbl;
Now, update the counter multiple times (in one transaction). The payload will only be executed once. Voilá!
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
SELECT * FROM x.tbl;
