Thread: fine grained trigger time specification...

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

On Tue, 23 Mar 2004 10:17:31 -0600
Bruno Wolff III <bruno@wolff.to> wrote:

> On Tue, Mar 23, 2004 at 15:19:13 +0100,
>   Erik Thiele <erik@thiele-hydraulik.de> wrote:
> > 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;
> 
> Wouldn't changing your triggers to be for each statement instead of for
> each row fix this?

no, because

1) my postgresql version does not support "for each statement"

2) i am also inserting with multiple insert commands thousands of entries.




-- 
Erik Thiele

On Tue, Mar 23, 2004 at 15:19:13 +0100, Erik Thiele <erik@thiele-hydraulik.de> wrote:
> 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;

Wouldn't changing your triggers to be for each statement instead of for
each row fix this?

On Tue, 23 Mar 2004, Erik Thiele wrote:

> On Tue, 23 Mar 2004 10:17:31 -0600
> Bruno Wolff III <bruno@wolff.to> wrote:
>
> > On Tue, Mar 23, 2004 at 15:19:13 +0100,
> >   Erik Thiele <erik@thiele-hydraulik.de> wrote:
> > > 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;

I didn't see the original, but...

One way to do hack this up is triggers which insert a row into another
holding table if and only if it hasn't already (something like transaction
id or some such - something you don't need to worry about concurrency
with) which itself has a deferred trigger on insert (which should probably
remove the row as part of its behavior).