[GENERAL] recursive inner trigger call
Hi guys, i got the following problematic : i got a table called bv that have some 'entry data' and i have another column that need to be calculated and put back in the table: here is my table: CREATE TABLE public.bv ( id_bv integer NOT NULL, c_vmax_actuel real, d_capacite_barrages_new real, CONSTRAINT BV_pkey PRIMARY KEY (id_bv) ) WITH ( OIDS=FALSE ); ALTER TABLE public.bv OWNER TO postgres; i created a trigger that do the necessary computation: CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$ DECLARE v_vmax_actuel numeric(15,2); BEGIN IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ; END IF; RETURN NEW; END; $store_bv$ LANGUAGE plpgsql; the declaration of my trigger : CREATE TRIGGER store_bv_trigger after INSERT OR UPDATE ON ed_explore.bv FOR EACH ROW EXECUTE PROCEDURE public.store_bv(); and now i start to insert my data: insert into public.bv (id_bv,d_capacite_barrages_new) values (1,7324591);commit; then the trigger got executed and goes in an infinite loop,here is the error that i got : ERREUR: dépassement de limite (en profondeur) de la pile HINT: Augmenter le paramètre « max_stack_depth » après vous être assuré que la limite de profondeur de la pile de la plateforme est adéquate. CONTEXT: instruction SQL « update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) » And thanks for you help
Re: [GENERAL] recursive inner trigger call
Hi Dave, when i use before trigger , nothing happen; i mean no cumputation is done (and when i start tu duplicate the same data just by mistake ...) i got the same error. From: Gauthier, Dave dave.gauth...@intel.com To: Red Light skydelt...@yahoo.com; pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Thursday, December 1, 2011 8:09 PM Subject: RE: [GENERAL] recursive inner trigger call You set the trigger to fire off whenever ed_expore.bv is inserted or updated. Then the trigger updates ed_explore.bv, which fires the update trigger again, etc... . Infinite loop. No? Maybe you just want to use a before trigger to set that value before the insert, then you wouldn't need the recursive after trigger? From:pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Red Light Sent: Thursday, December 01, 2011 1:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] recursive inner trigger call Hi guys, i got the following problematic : i got a table called bv that have some 'entry data' and i have another column that need to be calculated and put back in the table: here is my table: CREATE TABLE public.bv ( id_bv integer NOT NULL, c_vmax_actuel real, d_capacite_barrages_new real, CONSTRAINT BV_pkey PRIMARY KEY (id_bv) ) WITH ( OIDS=FALSE ); ALTER TABLE public.bv OWNER TO postgres; i created a trigger that do the necessary computation: CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$ DECLARE v_vmax_actuel numeric(15,2); BEGIN IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ; END IF; RETURN NEW; END; $store_bv$ LANGUAGE plpgsql; the declaration of my trigger : CREATE TRIGGER store_bv_trigger after INSERT OR UPDATE ON ed_explore.bv FOR EACH ROW EXECUTE PROCEDURE public.store_bv(); and now i start to insert my data: insert into public.bv (id_bv,d_capacite_barrages_new) values (1,7324591);commit; then the trigger got executed and goes in an infinite loop,here is the error that i got : ERREUR: dépassement de limite (en profondeur) de la pile HINT: Augmenter le paramètre « max_stack_depth » après vous être assuré que la limite de profondeur de la pile de la plateforme est adéquate. CONTEXT: instruction SQL « update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) » And thanks for you help
Re: [GENERAL] recursive inner trigger call
Hi David, Thanks a lot. From: David Johnston pol...@yahoo.com To: 'Red Light' skydelt...@yahoo.com; 'Gauthier, Dave' dave.gauth...@intel.com Cc: pgsql-general@postgresql.org Sent: Thursday, December 1, 2011 8:31 PM Subject: RE: [GENERAL] recursive inner trigger call CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$ DECLARE v_vmax_actuel numeric(15,2); BEGIN IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ; END IF; RETURN NEW; END; $store_bv$ LANGUAGE plpgsql; In the BEFORE trigger you cannot use an explicit “UPDATE …” statement. You want to do: NEW.c_vmax_actuel = ((NEW.d_capacite_barrages_new) / (30*86400)); To update the value of the in-context record directly within the trigger itself. If the table is at all large this is better anyway since you are not continually updating EVERY SINGLE RECORD regardless of whether it was recently changed. The IF statement, checking for INSERT or UPDATE, is redundant since you should only attach this function to triggers that only fire on insert or update. David J. From:pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Red Light Sent: Thursday, December 01, 2011 2:18 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] recursive inner trigger call Hi Dave, when i use before trigger , nothing happen; i mean no cumputation is done (and when i start tu duplicate the same data just by mistake ...) i got the same error. From:Gauthier, Dave dave.gauth...@intel.com To: Red Light skydelt...@yahoo.com; pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Thursday, December 1, 2011 8:09 PM Subject: RE: [GENERAL] recursive inner trigger call You set the trigger to fire off whenever ed_expore.bv is inserted or updated. Then the trigger updates ed_explore.bv, which fires the update trigger again, etc... . Infinite loop. No? Maybe you just want to use a before trigger to set that value before the insert, then you wouldn't need the recursive after trigger? From:pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Red Light Sent: Thursday, December 01, 2011 1:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] recursive inner trigger call Hi guys, i got the following problematic : i got a table called bv that have some 'entry data' and i have another column that need to be calculated and put back in the table: here is my table: CREATE TABLE public.bv ( id_bv integer NOT NULL, c_vmax_actuel real, d_capacite_barrages_new real, CONSTRAINT BV_pkey PRIMARY KEY (id_bv) ) WITH ( OIDS=FALSE ); ALTER TABLE public.bv OWNER TO postgres; i created a trigger that do the necessary computation: CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$ DECLARE v_vmax_actuel numeric(15,2); BEGIN IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ; END IF; RETURN NEW; END; $store_bv$ LANGUAGE plpgsql; the declaration of my trigger : CREATE TRIGGER store_bv_trigger after INSERT OR UPDATE ON ed_explore.bv FOR EACH ROW EXECUTE PROCEDURE public.store_bv(); and now i start to insert my data: insert into public.bv (id_bv,d_capacite_barrages_new) values (1,7324591);commit; then the trigger got executed and goes in an infinite loop,here is the error that i got : ERREUR: dépassement de limite (en profondeur) de la pile HINT: Augmenter le paramètre « max_stack_depth » après vous être assuré que la limite de profondeur de la pile de la plateforme est adéquate. CONTEXT: instruction SQL « update ed_explore.bv set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) » And thanks for you help
