stack depth limit exceeded PostgreSQL

Question

I have a Trigger that comes from the insert, update, delete,

in this update going wrong stack depth limit exceeded whenever an update is run and it goes into infinite loop. (infinite recursion)

anyone have any tips?

CREATE OR REPLACE FUNCTION saldolinear() RETURNS TRIGGER AS 
'
DECLARE
    IDS  INTEGER:= 0;   MIN integer:= 0; MIN1 integer:= 0; valor1 float;saldo float;    saldoini float; saldoini1 float;i integer:=0;j integer:= 0;
    l integer:=0;   m integer:=0;var2 integer:=0;countid integer:=0;coun integer:=0;DATADB date;IDMAX integer:=0;dataup date;
    coundata date;  res boolean;    res1 boolean;   mov TB_MOV_BANCARIA%rowtype; registro integer;
BEGIN
     IF (TG_OP = ''INSERT'' ) THEN
        SELECT INTO registro count(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<NEW.data group by TB_MOV_BANCARIA.data;
    saldoini:=0;        
    IF (registro>0) THEN    
       res:= true;
       SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<NEW.data;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;
    END IF;       
           FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=NEW.data order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
              saldoini := saldoini + mov.valor;
          UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
       END LOOP;
     END IF;
     IF (TG_OP = ''UPDATE'' ) THEN
        IF(OLD.data<NEW.data)THEN
       dataup:= OLD.data;
    ELSE
       dataup:= NEW.data;
        END IF;
        IF (NOT dataup is null) THEN
           SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<dataup;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;
       --RAISE EXCEPTION ''%'',dataup;
       FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=dataup order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
              saldoini := saldoini + mov.valor;
              --RAISE EXCEPTION ''UPDATE tb_mov_bancaria set saldo_linha=% where id = %;'',saldoini,mov.id;
          UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
       END LOOP;
      END IF;
     END IF;
      IF (TG_OP = ''DELETE'') THEN
       res:= true;
       SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<OLD.data;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where  tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;

           FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=OLD.data           order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
                saldoini := saldoini + mov.valor;
            UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
         END LOOP;
       END IF;
           RETURN NULL;
   END
  '
  LANGUAGE 'plpgsql'

The trigger runs for any insert/update/delete of table tb_mov_bancaria? — Mauren
– Mauren, Commented Oct 15, 2013 at 17:11
That is full of loops and horrbily linear logic for SQL...if this is being run for every insert/update, you are going to make this table nearly impossible to insert/update to. Only tip I can offer is to revisit the logic and see if you can do it without a loop — Twelfth
– Twelfth, Commented Oct 15, 2013 at 17:31

Chris Travers · Accepted Answer · 2013-11-28 09:08:47Z

1

I think you are doing the wrong thing with your trigger. A trigger should almost never recurse and if it recurses, you have to make darned sure it can't recurse infinitely.

Infinite recursion happens because you try to perform the operation again instead of merely modifying what the operation occurs on. You return null, which indicates you have already done everything you are intended to do.

Instead of redoing the update statements, etc. just return a NEW value including the modified data. Only use UPDATE or DELETE to modify tables (or if you absolutely must, rows) which are not modified by the original request.

answered Nov 28, 2013 at 9:08

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

stack depth limit exceeded PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related