Re: Triggers in pgadmin query tools
От | François Legrand |
---|---|
Тема | Re: Triggers in pgadmin query tools |
Дата | |
Msg-id | 4D8CF00D.5070004@lpnhe.in2p3.fr обсуждение исходный текст |
Ответ на | Triggers in pgadmin query tools (Francois Legrand <legrand@lpnhe.in2p3.fr>) |
Список | pgadmin-support |
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>
В списке pgadmin-support по дате отправления: