Re: Triggers in pgadmin query tools

Le 28/03/2011 11:18, Guillaume Lelarge a écrit :
> Le 28/03/2011 10:52, Francois Legrand a écrit :
> They are fired as many times as needed.
>
That's my understanding... but it seems it doesn't always act like this !

> There's no configuration that would explain that behaviour. My guess is
> that you have an issue with your trigger function. It would help to know
> its content, and your PostgreSQL release.

I finally figure out what was going on. Actually, my trigger function
was calling another stored procedure.
This stored procedure was defined as "IMMUTABLE" because it basically
returns the hostid of the machine hosting the database (thus as it never
changes, the returned value is always the same, it does not query the
database itself, and it makes sense to define it as immutable -see below
the definition of immutable).
BUT, it appears that this is the origin of my "problem". I changed the
stored proc to "volatile" and now the trigger is fired correctly.
It's tricky because it's not the trigger itself which was immutable but
a function called inside the trigger. I don't really understand why ; I
guess that the planner call the immutable procedure once by connection
and not anymore after (it keeps the result in memory) but it seems weird
that the trigger function (which calls this procedure) "inherits" it's
behaviour !

IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its argument list. If this option is given, any call
of the function with all-constant arguments can be immediately replaced
with the function value.
> BTW, this is more a PostgreSQL question, not a pgAdmin one.
>
I agree ! :-)
It just ended here because first I thought it was a problem with PGadmin !

F.