1

How can I find out if a trigger I have is recursing or looping in PostgreSQL? Is there an example of how to use it?

I'm looking for an example of how to add code to my current trigger to check to see if the cause of a failure is recursion.

Improve this question

1 Answer 1

Reset to default
2

You can RAISE NOTICE on pg_trigger_depth(); Here is an example where we also use 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);

Running this yields 

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

While this will assist in debugging the situation making a system dependent on this behavior is not a good idea.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.