Postgresql – Within a trigger is there a way to tell if an update or insert came from a trigger or not

postgresqltrigger

In PostgreSQL 9.3.5, I'm trying to prevent a trigger from executing again due to an update or insert within the current trigger or another trigger that executes upon update or insert. So I want to know what the source of the update is so I can prevent recursive triggering.

Unless I missed it, I looked here and didn't read anything about this in the manual:
https://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

I don't think this would work because I am thinking it will always return the trigger I am currently in, not the source (trigger or not) of the udpate or insert:

TG_NAME

Data type name; variable that contains the name of the trigger actually fired

Currently I am doing a lot of old.col_nm <> new.col_nm etc. to try and determine via the values that change if it was one of my triggers or not. It would be nice if I could just say "if an update or insert came from a trigger return new;" and skip the bulk of the trigger.

Best Answer

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is being fired from another trigger.

The only direct way to know analysing the execution stack during an exception:

DECLARE
  context text;
BEGIN
  BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS context = PG_EXCEPTION_CONTEXT;
  END;
  -- analyse here the context variable to find if the last item in the stack
  -- is this same function
END;

Exception handling is costly, but so are the other options, which require leaving a session marker for avoiding recursion, either using a temporary table, or a session variable:

BEGIN
  IF to_regclass('pg_temp.' || quote_ident(TG_NAME)) THEN -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  EXECUTE format('CREATE TEMP TABLE %I (a int) ON COMMIT DROP', TG_NAME);
  -- general body of trigger
  EXECUTE format('DROP TABLE pg_temp.%I', TG_NAME);
  RETURN NEW; -- or whatever appropriate
END;

or using a session variable:

BEGIN
  IF current_setting('x.' || TG_NAME, true) IS DISTINCT FROM '1' THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  PERFORM set_config('x.' || TG_NAME, '1', true);
  -- general body of trigger
  PERFORM set_config('x.' || TG_NAME, '', true);
  RETURN NEW; -- or whatever appropriate
END;

All of the options above have a significant performance impact. A possibly better performing option could involve a table for this purpose:

CREATE TABLE trigger_execs (
  pid int NOT NULL DEFAULT pg_backend_pid(),
  tgname name NOT NULL,
  PRIMARY KEY (pid, tgname)
);

which can be used like:

BEGIN
  IF EXISTS (SELECT 1 FROM trigger_execs
             WHERE pid = pg_backend_pid() AND tgname = TG_NAME) THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  INSERT INTO trigger_execs (tgname) VALUES (TG_NAME);
  -- general body of trigger
  DELETE FROM trigger_execs
  WHERE pid = pg_backend_pid() AND tgname = TG_NAME;
  RETURN NEW; -- or whatever appropriate
END;
Related Question