Re: [GENERAL] recursive inner trigger call

[Red Light]

Hi David,

Thanks a lot.




 From: David Johnston 
To: 'Red Light' ; "'Gauthier, Dave'" 
 
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" 
To: Red Light ; "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

[Bèrto ëd Sèra]

Hi Red,

I have the slight suspicion that you have not understood what NEW and OLD
mean, in the context of a trigger, am I correct?

See http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html

Bèrto

On 1 December 2011 22:17, Red Light  wrote:

>
> 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" 
> *To:* Red Light ; "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
>
>
>
>
>


-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

Re: [GENERAL] recursive inner trigger call

[David Johnston]

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" 
To: Red Light ; "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

[Red Light]

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" 
To: Red Light ; "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

[Gauthier, Dave]

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