Re: PostgreSQL triggers

On 1 Jul 2010, at 12:29, Sebastian Ritter wrote:

> 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.

It's quite common to insert RAISE NOTICE statements in your triggers to see what's going on.

> 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

If you mean the trigger just fired by the insert above, then yes. If you meant the trigger that called the current procedure, then no.

I don't think triggers get fired multi-threaded or even using multiple processes, so they can only run in the current process, sequentially.

> 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?

Are you talking about a BEFORE or an AFTER trigger? If it's an AFTER trigger, then the row firing the trigger has already been inserted and therefore is visible to the transaction.

> 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...

The outcome of what? A trigger doesn't return anything, it just calls a function of which the result determines what to do with the row data if it's a BEFORE trigger.

You could run into recursion using cascading triggers though. That may result in a backend crash if it's an endless loop, as there is a finite amount of stack space.

> 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?

It does time how long it takes for the command to complete. Since I'm quite sure triggers fire and execute sequentially, the command cannot complete until all trigger procedures finished executing. So, yes.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c2c93dc286215838022756!