Skip site navigation (1) Skip section navigation (2)

Re: Triggers in pgadmin query tools

From: François Legrand <legrand(at)lpnhe(dot)in2p3(dot)fr>
To: Michael Shapiro <mshapiro51(at)gmail(dot)com>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: Triggers in pgadmin query tools
Date: 2011-03-25 19:42:05
Message-ID: 4D8CF00D.5070004@lpnhe.in2p3.fr (view raw or flat)
Thread:
Lists: pgadmin-support
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
>
>

In response to

pgadmin-support by date

Next:From: François LegrandDate: 2011-03-25 19:42:35
Subject: Re: Triggers in pgadmin query tools
Previous:From: Guillaume LelargeDate: 2011-03-25 17:48:57
Subject: Re: Triggers in pgadmin query tools

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group