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(at)lpnhe(dot)in2p3(dot)fr <mailto:legrand(at)lpnhe(dot)in2p3(dot)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(at)postgresql(dot)org
> <mailto:pgadmin-support(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>
>