The problem
I spent today learning about triggers in postgresql - there are triggers "for each statement" and "for each row". I went searching for triggers "for each transaction", and found a few other folks with the same question but no compelling answer.
My use case is this - I have 2 tables, a parent (entries) and child (reports), with a one-to-many relationship. I need to add multiple reports for one entry in a single transaction, and then update the associated entry if the transaction succeeds. Here are the table definitions:
create schema transtest;
set searchpath to transtest,public;
-- it's nice to have a sandbox
-- remember to reset searchpath when done
create table myents ( id serial primary key, sum integer);
-- the entries table
create table myrecs ( id serial primary key, entid integer references myents (id), cases integer);
-- the reports table, many reports per entry
create table myids ( id integer primary key references myents (id));
-- table used to store trigger state
The solution
It appears that deferred constraint triggers are *almost* "per transaction". But constraint triggers are *always* for each row. Yet Postgresql 9.0 allows conditions on triggers - so all I need is to store some state of whether to fire the trigger or not for each row. First, the function that I'll be using in the condition:
-- function for when-clause (condition) in trigger
-- add id to the "to-do" list if it's not there already
-- return True only if it's been added to the least
create or replace function f_condition(myid integer) returns boolean as $_$
DECLARE
myret boolean := True;
newid integer;
BEGIN
RAISE NOTICE 'Return Value here is %', myret;
select count(*) into newid from myids where id = myid;
if newid > 0 then -- id is there, return False
myret := False;
else -- add id to queue, return True
insert into myids (id) values (myid);
end if;
return myret;
END;
$_$ language plpgsql;
Next, the trigger function, which will clean up after the above function when it's done:
-- trigger function to rebuild myent.sum for this myrecs.id
create or replace function f_refresh() returns trigger as $_$
DECLARE
mysum integer;
myid integer;
BEGIN
IF (TG_OP = 'DELETE') THEN
myid := OLD.entid;
ELSE
myid := NEW.entid;
END IF;
RAISE NOTICE 'Return Value here is %', myid;
select sum(cases) into mysum from myrecs where entid = myid;
-- compute entry sum from records and update it.
-- PL/pgSQL rules on queries are quirky. Read the docs.
update myents set sum = mysum where id = myid;
delete from myids where id = myid;
-- clean up
RETURN NULL;
END;
$_$ language plpgsql;
Finally, the trigger definitions using the above.
-- only fire trigger once per myent.id using when clause (condition)
-- eval is deferred until transaction concludes
create constraint trigger t_cache after insert or update
on myrecs initially deferred for each row
when ( f_condition(NEW.entid) ) -- comment out for pre pg 9.0
execute procedure f_refresh();
-- need 2, one for each for NEW (insert and update) and OLD (delete)
create constraint trigger t_cache_del after delete
on myrecs initially deferred for each row
when ( f_condition(OLD.entid) )
execute procedure f_refresh();
The test
All software needs to be tester, right?
I don't have pg9.0 yet, and it won't hit Ubuntu mainline until Ubuntu Natty, but it looks easy enough to install via a
backports repo. So, this isn't fully tested. Comment out the "when" lines in the trigger defines and it will work, albeit running once per row *after* the commit.
In any event, here goes! The sum should be zero until after the commit. If the
when clause works correctly, then the trigger should only fire (and emit a notice) once. Likewise, the
myids table should be empty.
insert into myents (sum) VALUES ( 0);
begin;
insert into myrecs (entid, cases) VALUES ( 1, 0), (1, 1), (1,2);
select * from myents; -- 0
select * from myids; -- entid from above
commit;
select * from myents; -- new sum
select * from myids; -- empty
References -- Postgresql Docs:
Posts
