Date: Tue, 23 Mar 2004 15:19:13 +0100
From: Erik Thiele <erik@thiele-hydraulik.de>
To: pgsql-sql@postgresql.org
Subject: fine grained trigger time specification...
Message-Id: <20040323151913.13de3fa7.erik@thiele-hydraulik.de>
Organization: Thiele-Hydraulik
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

hi,

thanks to Stephan Szabo, Achilleus Mantzios and Bruno Wolff III i
managed to fix my problem with special integrity constraints.

i have created a big function "alwayscheck" that checks various
conditions on two tables in order to ensure their consistency with each
other (no this is not doable with foreign key constraints, since my
special constraints are complicated mathematical stuff).

now with the commands


create constraint trigger a_foo1 after insert or update or delete on a
initially deferred for each row execute procedure alwayscheck();

create constraint trigger b_foo1 after insert or update or delete on b
initially deferred for each row execute procedure alwayscheck();


from Stephan Szabo :-) (thx!) this big procedure gets called right on
commit time, and only
if at least one of the two tables was modified.

of course i cannot readback my pg_dump generated backups, but i added a
global switch to deactivate all consistency checking for this task. of
course only the superuser may set this flag.


now sadly i am getting this kind of problem:


zeit=> insert into a select
nextval('delmeseq'),personalnumber,datum,datum from calendar where
type=10409;
INSERT 0 581   <-- see, 581 inserts which is pretty much :)
zeit=> commit;  <--  now i have to wait for 581 calls of alwayscheck


since alwayscheck is not the fastest function on earth, i'd like to have
it called on commit time if at least one of table a or b was modified,
but always only once per commit. is there a way to formulate this:

ARRANGE FOR CALL OF FUNCTION alwayscheck() ON COMMIT WHEN AT LEAST ONE
OF TABLE a OR TABLE b WAS MODIFIED BUT CALL THE FUNCTION AT MOST ONE
TIME;


cya & thx again for all previous ideas
erik

-- 
Erik Thiele