pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfine grained trigger time specification...
4+ messages / 3 participants
[nested] [flat]
* fine grained trigger time specification...
@ 2004-03-23 14:19 Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 16:17 ` Re: fine grained trigger time specification... Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Thiele @ 2004-03-23 14:19 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: fine grained trigger time specification...
2004-03-23 14:19 fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
@ 2004-03-23 16:17 ` Bruno Wolff III <bruno@wolff.to>
2004-03-23 16:56 ` Re: fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2004-03-23 16:17 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: fine grained trigger time specification...
2004-03-23 14:19 fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 16:17 ` Re: fine grained trigger time specification... Bruno Wolff III <bruno@wolff.to>
@ 2004-03-23 16:56 ` Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 17:22 ` Re: fine grained trigger time specification... Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Thiele @ 2004-03-23 16:56 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: fine grained trigger time specification...
2004-03-23 14:19 fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 16:17 ` Re: fine grained trigger time specification... Bruno Wolff III <bruno@wolff.to>
2004-03-23 16:56 ` Re: fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
@ 2004-03-23 17:22 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-03-23 17:22 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
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).
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-23 17:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-23 14:19 fine grained trigger time specification... Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 16:17 ` Bruno Wolff III <bruno@wolff.to>
2004-03-23 16:56 ` Erik Thiele <erik@thiele-hydraulik.de>
2004-03-23 17:22 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox