postgresql - 如何在 PostgreSQL 中调试触发器递归？

Question

如何确定我拥有的触发器在 PostgreSQL 中是递归的还是循环的？ 有使用示例吗？

我正在寻找如何 add code to my current trigger 的示例检查失败的原因是否是递归。

Answer 1

您可以 RAISE NOTICE在 pg_trigger_depth();这是一个我们也使用 TG_NAME 的例子

CREATE TABLE foo (id int primary key);

CREATE FUNCTION bar()
RETURNS trigger
AS $$
  BEGIN
    -- Debugging information
    RAISE NOTICE '[%] TRIGGER DEPTH %', TG_NAME, pg_trigger_depth();
    INSERT INTO foo VALUES (42);
    RETURN null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER bar
  AFTER INSERT ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE bar();

INSERT INTO foo VALUES (41);

运行这个产生

NOTICE:  [bar] TRIGGER DEPTH 1
NOTICE:  [bar] TRIGGER DEPTH 2
ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (id)=(42) already exists.
CONTEXT:  SQL statement "INSERT INTO foo VALUES (42)"
PL/pgSQL function bar() line 5 at SQL statement
SQL statement "INSERT INTO foo VALUES (42)"
PL/pgSQL function bar() line 5 at SQL statement

虽然这将有助于调试情况 making a system dependent on this behavior is not a good idea .