Hi All,
I was hoping you could help with a few queries regarding row-wise
PostgreSQL triggers.
Is it possible to see triggers appearing in the postgresql-8.3-main.log?
I have "log_min_duration_statement" set to 0 (logs all statements) in my
postgresql.conf but I can't seem to find any reference to triggers being
executed.
My next questions are best illustrated by the following example:
A PostgreSQL PL/pgSQL function makes multiple consecutive inserts on a
table that has a ROW-WISE INSERT trigger. The INSERT trigger executes a
function to delete historical entries in the same table.
1. What is the order of execution between the multiple inserts and their
corresponding trigger invocations? Do the triggers run synchronously or
asynchronously from their respective inserts ?
Synchronously:
INSERT ROW
WAIT TO FINISH EXECUTION OF TRIGGER
INSERT NEXT ROW
WAIT TO FINISH EXECUTION OF TRIGGER
INSERT NEXT ROW ....
Asynchronously:
INSERT ROW -> INVOKE EXECUTION OF TRIGGER
INSERT NEXT ROW -> INVOKE EXECUTION AGAIN (first invocation of trigger may still be running)
INSERT NEXT ROW ....
I think I'm getting caught out by a trigger invocation not finishing
before a later insert is made, and thinking that the newly inserted row
is "historical", causing it to be deleted. Is this possible? If so, is
there a way of simulating the synchronous approach described above?
2. Does having cascading triggers influence the outcome in any way? Many
of our triggers (including the one above) manipulate rows in different
tables which in turn fire more triggers...
3. If I execute the PL/pgSQL function within Postgres with "\timing" set,
will the returned execution time include the duration of the invoked
triggers?
Kindest regads,
Sebastian
--
Sebastian Ritter
Software Manager
sebastian@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928