database design - Execute deferred trigger only once per row in PostgreSQL

Question

I have a deferred AFTER UPDATE trigger on a table, set to fire when a certain column is updated. It's an integer type I'm using as a counter.

I'm not 100% certain but it looks like if I increment that particular column 100 times during a transaction, the trigger is queued up and executed 100 times at the end of the transaction.

I would like the trigger to only be scheduled once per row no matter how many times I've incremented that column.

Can I do that somehow? Alternatively if triggered triggers must queue up regardless if they are duplicates, can I clear this queue during the first run of the trigger?

Version of Postgres is 9.1. Here's what I got:

CREATE CONSTRAINT TRIGGER counter_change
    AFTER UPDATE OF "Counter" ON "table"
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE counter_change();

CREATE OR REPLACE FUNCTION counter_change()
    RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN

PERFORM some_expensive_procedure(NEW."id");

RETURN NEW;

END;$$;

Answer 1

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;