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.