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

Question

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.

Answer 1

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;

Answer 2

This isn't the solution I was expecting, but it works great to stop recursive trigger execution (aka trigger cascading):

CREATE TRIGGER trg_nm
    BEFORE INSERT OR UPDATE
    ON tbl_nm
    FOR EACH ROW
    when (pg_trigger_depth() = 0)
    EXECUTE PROCEDURE nm_trg_func();

Just run that when you create your trigger after you have created your trigger function (in this case nm_trg_func()).

I found it here, agnessa's answer:

• Prevent recursive trigger in PostgreSQL

The key is this function:

pg_trigger_depth()

Read about it here:
https://www.postgresql.org/docs/9.3/static/functions-info.html

I only tested it against stopping the same trigger from executing, not a different trigger (not across different triggers). But that works great!