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