Thread: 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
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>
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.