From: Erik Thiele <erik@thiele-hydraulik.de>
To: pgsql-sql@postgresql.org
Subject: fine grained trigger time specification...
Date: Tue, 23 Mar 2004 15:19:13 +0100
Message-ID: <20040323151913.13de3fa7.erik@thiele-hydraulik.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@thiele-hydraulik.de
Subject: Re: fine grained trigger time specification...
In-Reply-To: <20040323151913.13de3fa7.erik@thiele-hydraulik.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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