The problemI 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 solutionIt 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 testAll 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