Skip site navigation (1) Skip section navigation (2)

fine grained trigger time specification...

From: Erik Thiele <erik(at)thiele-hydraulik(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: fine grained trigger time specification...
Date: 2004-03-23 14:19:13
Message-ID: 20040323151913.13de3fa7.erik@thiele-hydraulik.de (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-03-23 14:54:02
Subject: Re: Sorting an aggregated column
Previous:From: Martin MarquesDate: 2004-03-23 14:04:12
Subject: Re: inverse of "day of year"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group