Re: Triggers in pgadmin query tools

You're right. I run psql directly on the database host and access this database using pgadmin from a remote machine. I
couldtry to run pgadmin on the same computer too, but I am pretty sure that it won't change the result.<br /> The
strangepoint is that if I open the table using "view content of the table" in pgadmin and add a line in the table, then
thetrigger works. Thus it appears that it's just using the Query tool that the problems appears !<br /> It looks like
ifthe query tool was working in a session_replication_role set to replica instead of origin !<br /> But your second
pointis interesting : I don't know if the trigger is not fired or fails. I will have a look at my logs on Monday !<br
/><br/> Le 25/03/2011 18:36, Michael Shapiro a écrit : <blockquote
cite="mid:AANLkTi=siVfsLr4zPU-CanN+M6-XFQ57hVNpG4b0_MhT@mail.gmail.com"type="cite">Are you running PgAdmin on the same
machineas psql?<br /> How do you know the trigger isn't fired? Perhaps it is firing, but failing?<br /><br /><div
class="gmail_quote">OnFri, Mar 25, 2011 at 11:54 AM, Francois Legrand <span dir="ltr"><<a
href="mailto:legrand@lpnhe.in2p3.fr"moz-do-not-send="true">legrand@lpnhe.in2p3.fr</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt         0.8ex; border-left: 1px solid rgb(204, 204, 204);
     padding-left: 1ex;">Hi all,<br /> I have a table with a trigger. When I run an insert command from psql, the
triggeris correctly fired, but if i run exactly the same command from the pgqdmin3 query tool, the trigger is not fired
!<br/> Is that normal ?<br /> Any clue ?<br /><br /> F.<br />
---------------------------------------------------------------------------------------------------------------<br/> PS
:Here are the definitions :<br />
---------------------------------------------------------------------------------------------------------------<br/>
CREATETABLE databases<br /> (<br />  id_databases serial NOT NULL,<br />  hostname character varying,<br />  hostid
charactervarying,<br />  dbname character varying,<br />  comments character varying,<br />  CONSTRAINT databases_pkey
PRIMARYKEY (id_databases),<br />  CONSTRAINT db_is_unique UNIQUE (hostname, hostid, dbname)<br /> )<br /> WITH (<br />
 OIDS=TRUE<br/> );<br /><br />
---------------------------------------------------------------------------------------------------------------<br/>
CREATETRIGGER tai_databases0<br />  AFTER INSERT OR UPDATE OR DELETE<br />  ON databases<br />  FOR EACH ROW<br />
 EXECUTEPROCEDURE update_sequences();<br /><br />
---------------------------------------------------------------------------------------------------------------<br/>
CREATEOR REPLACE FUNCTION update_sequences()<br />  RETURNS trigger AS<br /> $BODY$<br />    DECLARE<br />    id_db_
int8;<br />    pos int8;<br />    shift_ int8 ;<br />    min_ int8;<br />    max_ int8;<br />    nextval_ int8;<br />  
 newval_    int8;<br />    seq varchar ='';<br />    sequences_ varchar[] ;<br />    BEGIN<br /><br />    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_;<br
/><br/>    SELECT * FROM get_id_db() INTO id_db_  ;<br />    shift_:=10^12;<br />    min_:=shift_*id_db_;<br />  
 max_:=shift_*(id_db_+1)-1;<br/><br />    FOR pos IN 1 .. array_upper(sequences_, 1) LOOP<br />      
 seq=sequences_[pos]||'_id_'||sequences_[pos]||'_seq';<br/>        SELECT nextval(seq) INTO nextval_ ;<br />        IF
((nextval_> max_) OR (nextval_ < min_)) THEN<br />            SELECT setval(seq,min_,'true') INTO newval_;<br />
      END IF;<br />    END LOOP;<br /><br />    RETURN NEW;<br /><br />    END;<br /> $BODY$<br />  LANGUAGE 'plpgsql'
VOLATILESECURITY DEFINER<br />  COST 100;<br /><br />
---------------------------------------------------------------------------------------------------------------<br/><br
/>CREATE OR REPLACE FUNCTION get_id_db()<br />  RETURNS bigint AS<br /> $BODY$<br />    DECLARE<br />    id_db_ int8
;<br/>    BEGIN<br />    id_db_:=0;<br /><br />    SELECT id_databases INTO id_db_<br />    FROM  databases<br />  
 WHEREdatabases.hostname=hostname()<br />    AND databases.hostid=hostid()<br />    AND
databases.dbname=current_database()  ;<br /><br />    IF  id_db_ IS NULL THEN<br />    id_db_:=0;<br />    END IF;<br
/><br/>    RETURN id_db_;<br /><br />    END;<br /> $BODY$<br />  LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER<br />
 COST100;<br /><br /><br /><br />
---------------------------------------------------------------------------------------------------------------<br/>
CREATEOR REPLACE FUNCTION hostid()<br />  RETURNS character varying AS<br /> $BODY$<br /> #!/bin/sh<br /> hostid<br />
$BODY$<br/>  LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER<br />  COST 100;<br /><br /><br />
---------------------------------------------------------------------------------------------------------------<br/><br
/><br/> CREATE OR REPLACE FUNCTION hostname()<br />  RETURNS character varying AS<br /> $BODY$<br /> #!/bin/sh<br />
hostname<br/> $BODY$<br />  LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER<br />  COST 100;<br /><font color="#888888"><br
/><br/><br /> -- <br /> Sent via pgadmin-support mailing list (<a href="mailto:pgadmin-support@postgresql.org"
moz-do-not-send="true"target="_blank">pgadmin-support@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgadmin-support" moz-do-not-send="true"
target="_blank">http://www.postgresql.org/mailpref/pgadmin-support</a><br/></font></blockquote></div><br
/></blockquote>