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