Обсуждение: Triggers in pgadmin query tools

Hi all,
I have a table with a trigger. When I run an insert command from psql, 
the trigger is correctly fired, but if i run exactly the same command 
from the pgqdmin3 query tool, the trigger is not fired !
Is that normal ?
Any clue ?

F.
---------------------------------------------------------------------------------------------------------------
PS : Here are the definitions :
---------------------------------------------------------------------------------------------------------------
CREATE TABLE databases
(  id_databases serial NOT NULL,  hostname character varying,  hostid character varying,  dbname character varying,
commentscharacter varying,  CONSTRAINT databases_pkey PRIMARY KEY (id_databases),  CONSTRAINT db_is_unique UNIQUE
(hostname,hostid, dbname)
 
)
WITH (  OIDS=TRUE
);

---------------------------------------------------------------------------------------------------------------
CREATE TRIGGER tai_databases0  AFTER INSERT OR UPDATE OR DELETE  ON databases  FOR EACH ROW  EXECUTE PROCEDURE
update_sequences();

---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_sequences()  RETURNS trigger AS
$BODY$    DECLARE    id_db_ int8 ;    pos int8;    shift_ int8 ;    min_ int8;    max_ int8;    nextval_ int8;
newval_    int8;    seq varchar ='';    sequences_ varchar[] ;    BEGIN
 
    Select ARRAY['dbimage', 'exposure_file', 
'exposure','dead','bias','flat','photflat','night','detrendset','subtraction', 
'reference','release','access_mode','data_store','scheduler','object_type','log','code','action'] 
INTO sequences_;
    SELECT * FROM get_id_db() INTO id_db_  ;    shift_:=10^12;    min_:=shift_*id_db_;    max_:=shift_*(id_db_+1)-1;
    FOR pos IN 1 .. array_upper(sequences_, 1) LOOP        seq=sequences_[pos]||'_id_'||sequences_[pos]||'_seq';
SELECTnextval(seq) INTO nextval_ ;        IF ((nextval_ > max_) OR (nextval_ < min_)) THEN            SELECT
setval(seq,min_,'true')INTO newval_;        END IF;    END LOOP;
 
    RETURN NEW;
    END;
$BODY$  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER  COST 100;

---------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION get_id_db()  RETURNS bigint AS
$BODY$    DECLARE    id_db_ int8 ;    BEGIN    id_db_:=0;
    SELECT id_databases INTO id_db_    FROM  databases    WHERE databases.hostname=hostname()    AND
databases.hostid=hostid()   AND databases.dbname=current_database()   ;
 
    IF  id_db_ IS NULL THEN    id_db_:=0;    END IF;
    RETURN id_db_;
    END;
$BODY$  LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER  COST 100;



---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hostid()  RETURNS character varying AS
$BODY$
#!/bin/sh
hostid
$BODY$  LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER  COST 100;


---------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE FUNCTION hostname()  RETURNS character varying AS
$BODY$
#!/bin/sh
hostname
$BODY$  LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER  COST 100;

Le 25/03/2011 17:54, Francois Legrand a écrit :
> Hi all,
> I have a table with a trigger. When I run an insert command from psql,
> the trigger is correctly fired, but if i run exactly the same command
> from the pgqdmin3 query tool, the trigger is not fired !
> Is that normal ?
> Any clue ?
> 

Are you sure you're connected to the same database?


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

You're right. I run psql directly on the database host and access this database using pgadmin from a remote machine. I could try to run pgadmin on the same computer too, but I am pretty sure that it won't change the result.
The strange point is that if I open the table using "view content of the table" in pgadmin and add a line in the table, then the trigger works. Thus it appears that it's just using the Query tool that the problems appears !
It looks like if the query tool was working in a session_replication_role set to replica instead of origin !
But your second point is interesting : I don't know if the trigger is not fired or fails. I will have a look at my logs on Monday !

Le 25/03/2011 18:36, Michael Shapiro a écrit :

Are you running PgAdmin on the same machine as psql?
How do you know the trigger isn't fired? Perhaps it is firing, but failing?

On Fri, Mar 25, 2011 at 11:54 AM, Francois Legrand <legrand@lpnhe.in2p3.fr> wrote:

Hi all,
I have a table with a trigger. When I run an insert command from psql, the trigger is correctly fired, but if i run exactly the same command from the pgqdmin3 query tool, the trigger is not fired !
Is that normal ?
Any clue ?

F.
---------------------------------------------------------------------------------------------------------------
PS : Here are the definitions :
---------------------------------------------------------------------------------------------------------------
CREATE TABLE databases
(
 id_databases serial NOT NULL,
 hostname character varying,
 hostid character varying,
 dbname character varying,
 comments character varying,
 CONSTRAINT databases_pkey PRIMARY KEY (id_databases),
 CONSTRAINT db_is_unique UNIQUE (hostname, hostid, dbname)
)
WITH (
 OIDS=TRUE
);

---------------------------------------------------------------------------------------------------------------
CREATE TRIGGER tai_databases0
 AFTER INSERT OR UPDATE OR DELETE
 ON databases
 FOR EACH ROW
 EXECUTE PROCEDURE update_sequences();

---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_sequences()
 RETURNS trigger AS
$BODY$
   DECLARE
   id_db_ int8 ;
   pos int8;
   shift_ int8 ;
   min_ int8;
   max_ int8;
   nextval_ int8;
   newval_     int8;
   seq varchar ='';
   sequences_ varchar[] ;
   BEGIN

   Select ARRAY['dbimage', 'exposure_file', 'exposure','dead','bias','flat','photflat','night','detrendset','subtraction', 'reference','release','access_mode','data_store','scheduler','object_type','log','code','action'] INTO sequences_;

   SELECT * FROM get_id_db() INTO id_db_  ;
   shift_:=10^12;
   min_:=shift_*id_db_;
   max_:=shift_*(id_db_+1)-1;

   FOR pos IN 1 .. array_upper(sequences_, 1) LOOP
       seq=sequences_[pos]||'_id_'||sequences_[pos]||'_seq';
       SELECT nextval(seq) INTO nextval_ ;
       IF ((nextval_ > max_) OR (nextval_ < min_)) THEN
           SELECT setval(seq,min_,'true') INTO newval_;
       END IF;
   END LOOP;

   RETURN NEW;

   END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
 COST 100;

---------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION get_id_db()
 RETURNS bigint AS
$BODY$
   DECLARE
   id_db_ int8 ;
   BEGIN
   id_db_:=0;

   SELECT id_databases INTO id_db_
   FROM  databases
   WHERE databases.hostname=hostname()
   AND databases.hostid=hostid()
   AND databases.dbname=current_database()   ;

   IF  id_db_ IS NULL THEN
   id_db_:=0;
   END IF;

   RETURN id_db_;

   END;
$BODY$
 LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER
 COST 100;



---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hostid()
 RETURNS character varying AS
$BODY$
#!/bin/sh
hostid
$BODY$
 LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER
 COST 100;


---------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE FUNCTION hostname()
 RETURNS character varying AS
$BODY$
#!/bin/sh
hostname
$BODY$
 LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER
 COST 100;



--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Absolutely !

Le 25/03/2011 18:48, Guillaume Lelarge a écrit :
> Le 25/03/2011 17:54, Francois Legrand a écrit :
>> Hi all,
>> I have a table with a trigger. When I run an insert command from psql,
>> the trigger is correctly fired, but if i run exactly the same command
>> from the pgqdmin3 query tool, the trigger is not fired !
>> Is that normal ?
>> Any clue ?
>>
> Are you sure you're connected to the same database?
>
>

Hi all,<br /> Are triggers are fired "only once by connection" ?<br /> Here is my point.<br /><br /> I found a really
strangebehaviour of the pgadmin II with triggers.<br /> I activated the detailed logs on my server thus I could monitor
everything,and the point is the following :<br /><br /> 1) I open the query tool<br /> 2) I do a first insert in my
table==> The trigger is fired<br /> 3) I do a second (and more) insert in my table ==> The trigger is NOT fired
<br/> 4) I close the query tool and open it again and do an insert ==> the trigger is fired<br /><br /> Thus it
appearsthat when I run several inserts from the query tool, the trigger is fired the first time but not anymore
after.<br/> I found that this is exactly the same if I add lines directly from the "Edit data" window. The first line I
addfire the trigger, but the others no.<br /><br /> Moreover, it is also the same from the psql tool. <br /> I open
psql,do an insert : the trigger is fired. But if I do a second insert, the trigger is not fired.<br /><br /><b>Thus it
appearsthat triggers are fired "only once by connection" !</b><br /><br /> I found that someone had the same problem
(<aclass="moz-txt-link-freetext"
href="http://www.mail-archive.com/pgsql-general@postgresql.org/msg23094.html">http://www.mail-archive.com/pgsql-general@postgresql.org/msg23094.html</a>)<br
/><br/> Is that normal ? Is it possible to fix that in the server configuration ?<br /><br /> I would appreciate some
feedbackon this issue !<br /> Thanks in advance<br /><br /> F.<br /><br /><br /><br /><br /><br /> Le 25/03/2011 18:48,
GuillaumeLelarge a écrit : <blockquote cite="mid:4D8CD589.4000404@lelarge.info" type="cite"><pre wrap="">Le 25/03/2011
17:54,Francois Legrand a écrit :
 
</pre><blockquote type="cite"><pre wrap="">Hi all,
I have a table with a trigger. When I run an insert command from psql,
the trigger is correctly fired, but if i run exactly the same command
from the pgqdmin3 query tool, the trigger is not fired !
Is that normal ?
Any clue ?

</pre></blockquote><pre wrap="">
</pre></blockquote>

Le 28/03/2011 10:52, Francois Legrand a écrit :
> Hi all,
> Are triggers are fired "only once by connection" ?

They are fired as many times as needed.

> Here is my point.
> 
> I found a really strange behaviour of the pgadmin II with triggers.
> I activated the detailed logs on my server thus I could monitor
> everything, and the point is the following :
> 
> 1) I open the query tool
> 2) I do a first insert in my table ==> The trigger is fired
> 3) I do a second (and more) insert in my table ==> The trigger is NOT fired
> 4) I close the query tool and open it again and do an insert ==> the
> trigger is fired
> 
> Thus it appears that when I run several inserts from the query tool, the
> trigger is fired the first time but not anymore after.
> I found that this is exactly the same if I add lines directly from the
> "Edit data" window. The first line I add fire the trigger, but the
> others no.
> 
> Moreover, it is also the same from the psql tool.
> I open psql, do an insert : the trigger is fired. But if I do a second
> insert, the trigger is not fired.
> 
> *Thus it appears that triggers are fired "only once by connection" !*
> 
> I found that someone had the same problem
> (http://www.mail-archive.com/pgsql-general@postgresql.org/msg23094.html)
> 

This mail was sent in 2001, that's only ten years back. Things have
changed since (btw, I'm not saying it worked this way at this time).

> Is that normal ? Is it possible to fix that in the server configuration ?
> 

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.

BTW, this is more a PostgreSQL question, not a pgAdmin one.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

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.