sql - 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();

当我尝试插入时，它返回给我:

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

返回:

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

Answer 1

您的触发器一遍又一遍地插入同一行。有多种方法可以防止这种情况发生。喜欢:

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();

参见:

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

EXECUTE FUNCTION 需要 Postgres 11。参见:

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

但是您似乎只想禁止pg_inicial >= pg_final。您可以使用 CHECK 约束来做到这一点:

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

CHECK 约束更简单、更快且更可靠。见:

• Trigger vs. check constraint
• How much cost check constraints in Postgres 9.x?

当然，在违反时引发异常，这通常是要走的路。
默默地做，你又回到了触发器。更简单:

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$;

要正常进行 INSERT/UPDATE，BEFORE 触发器必须 RETURN NEW;。 RETURN NULL 取消该行。