Stack depth limit exceeded in Postgres

Question

CREATE OR REPLACE FUNCTION verificar_pagina_inicial_final()
  RETURNS trigger AS
$BODY$
BEGIN
IF NEW.pg_inicial < NEW.pg_final THEN
 INSERT INTO artigos(id_artigo,id_editora,tipo_artigo,pg_inicial,pg_final)
VALUES(NEW.id_artigo,NEW.id_editora,NEW.tipo_artigo,NEW.pg_inicial,NEW.pg_final);
END IF;

END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verifiar_paginas_novo_artigo
  BEFORE INSERT OR UPDATE
  ON artigos
  FOR EACH ROW
  EXECUTE PROCEDURE  verificar_pagina_inicial_final();

When I try to insert it returns me this:

INSERT INTO public.artigos(id_artigo, id_editora, tipo_artigo, pg_inicial, pg_final)
VALUES (30, 3, 'teste', 1, 2);

Returns:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "INSERT INTO artigos(id_artigo,id_editora,tipo_artigo,pg_inicial,pg_final)
VALUES(NEW.id_artigo,NEW.id_editora,NEW.tipo_artigo,NEW.pg_inicial,NEW.pg_final)"
PL/pgSQL function verificar_pagina_inicial_final() line 4 at SQL statement

It seems that your trigger and procedure form an infinite recursion. `ON INSERT` trigger calls SP that itself performs `INSERT` on the same table. — PM 77-1, Jun 12 '20 at 00:18
thanks for answering ... so when he inserts in the function he calls the trigger again? is there any way I can do this just once? — ramos jc, Jun 12 '20 at 00:35

Erwin Brandstetter · Answer 1 · 2020-06-12T09:14:07.777

Your trigger inserts the same row over and over again. There are various ways to prevent this. Like:

CREATE TRIGGER verifiar_paginas_novo_artigo
BEFORE INSERT OR UPDATE ON artigos
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)  -- !
EXECUTE FUNCTION verificar_pagina_inicial_final();

See:

How to prevent a PostgreSQL trigger from being fired by another trigger?

EXECUTE FUNCTION requires Postgres 11. See:

Trigger function does not exist, but I am pretty sure it does

But it seems you just want to disallow pg_inicial >= pg_final. You could do that with a CHECK constraint:

ALTER TABLE artigos ADD CONSTRAINT pg_final_must_be_greater_than_pg_inicial
CHECK (pg_inicial < pg_final);

A CHECK constraint is simpler, faster, and more reliable. See:

Raises an exception when violated, of course, which typically is the way to go.
To do it silently, you are back to triggers. Just simpler:

CREATE OR REPLACE FUNCTION verificar_pagina_inicial_final()
  RETURNS trigger LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.pg_inicial < NEW.pg_final THEN
      RETURN NEW;   -- proceed
   ELSE
      RETURN NULL;  -- skip insert / update
   END IF;
END
$func$;

To proceed normally with an INSERT / UPDATE, a BEFORE trigger must RETURN NEW;. RETURN NULL cancels the row.

Harald · Answer 2 · 2020-06-12T01:38:33.777

Assuming this is just a test before an insertion as you do not modify the data, maybe

IF (NEW.pg_inicial < NEW.pg_final) THEN
      RETURN NEW;
ELSE
      RETURN NULL;
END IF;

p.s: I'll make a DB fiddle to check if this syntax is OK. here is the fiddle, https://www.db-fiddle.com/f/9iDx4bmJdk5rUzPrFdevAt/2 .

Edit2: Erwin Brandstetter's answer is complete, you should add a constraint instead of this trigger.

score 0 · Answer 3 · answered Jun 12 '20 at 01:22

Since you are "Inserting" only new columns, the insert, in the trigger function, is completely unnecessary - that's what is going to be inserted anyway. The question becomes what do you want then IF NEW.pg_inicial < NEW.pg_final" returns False. You have basically 3 options:

Do nothing, in which case just do not have a trigger at all.
Abort the complete insert statement. In this case create a check constraint and do not have a trigger at all. Alternately "Raise Exception" in the trigger, but that is not a good plan.
Silently ignore (skip) this particular row but otherwise continue see documentation

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).

In which implement the trigger function as follows:

create or replace 
function verificar_pagina_inicial_final()
  returns trigger 
  language plpgsql
as $$
begin
   if new.pg_inicial < new.pg_final 
   then
       return new;
   else 
       return null; 
   end if; 
end;
$$ ;

Stack depth limit exceeded in Postgres

3 Answers3