Re: PostgreSQL triggers

Hi Alban,

Thanks for your response and your time. I added some more explanatory text in-line.

Kindest Regards,
Sebastian

On Thu, Jul 01, 2010 at 03:10:01PM +0200, Alban Hertroys wrote:
> 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 was indeed referring to the to insert above.
>
> I don't think triggers get fired multi-threaded or even using multiple processes, so they can only run in the current
process,sequentially. 

That's a relief. I couldn't find any docs on this anywhere. Do you know,
by chance, if any exist?
>
> > 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
alreadybeen inserted and therefore is visible to the transaction. 

The trigger is an AFTER trigger but I'm not worried about the row firing
the trigger being deleted.

I'll try and explain my worries with a concrete example:

Suppose my stored procedure inserts colors into an initial empty table
in the following order:

INSERT INTO TABLE colors (color) VALUES ('Red');
INSERT INTO TABLE colors (color) VALUES ('Green');
INSERT INTO TABLE colors (color) VALUES ('Blue');

Let's say the associated ROW-WISE AFTER INSERT trigger does the
following:

If the color of the row just inserted is 'Red' find any rows with color
'Blue' and delete them.

(In reality, the trigger and table are much more complex).

I thought that maybe the following was happening:

When the colour "Red" is inserted, invoking the trigger, the trigger
starts a complicated scan on the colors table. In the meantime the
stored procedure keeps inserting the remaining color rows, including the
"Blue" color.

When the first trigger instance (triggered by color "Red") has finally
finished the scan, it finds the newly inserted Blue color and deletes
it.

But if triggers don't get fired multi-threaded then they must be
sequential and this could never happen. There must be a logic error in
the system somewhere, but I wanted to assure myself that this is the
case.

Could there be any difference between inserting individual rows as above
or doing a single multy insert as below?

INSERT INTO colors (SELECT
                        color
                    FROM
                        another_colors_table);
>
> > 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
todo with the row data if it's a BEFORE trigger. 
I was just wondering if a cascaded tigger could potentially be run in a different thread.
>
> 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. 

I did indeed run into this at the begining! I have all the appropriate stop conditions in place now.
>
> > 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!
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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