title: PostgreSQL trigger not firing. Can you see anything wrong?
body: <p>Wrote this trigger 5-6 months ago, and it ran fine on the db I deployed it to. Just took it off the shelf, and pretty much dropped it on the same database which lives on a different box. Sucker isn't executing anymore. </p>
  
  <p>Essentially, this trigger just copies the "before" row to an audit table. The audit table lives in a different db on the same server, and I link it back to the source database as a FOREIGN Table: </p>
  
  <p>Audit db: </p>
  
  <pre><code>CREATE TABLE next_gen_permissions_change
  (
    id serial NOT NULL,
    authorizable_type character varying(64),
    authorizable_id integer,
    grantee_id integer,
    grantee_type character varying(255),
    capability_id integer NOT NULL,
    permission integer DEFAULT 0,
    event_time timestamp without time zone,
    event_type character varying(10)
  )
  GRANT ALL ON TABLE next_gen_permissions_change TO foo;
  </code></pre>
  
  <p>I create the foreign table in the "source" database: </p>
  
  <pre><code>CREATE EXTENSION postgres_fdw;
  CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'audit', port '5432');
  CREATE USER MAPPING FOR foo SERVER myserver OPTIONS (user 'foo', password 'secret');
  
  CREATE foreign TABLE next_gen_permissions_change
  (
    id serial NOT NULL,
    authorizable_type character varying(64),
    authorizable_id integer,
    grantee_id integer,
    grantee_type character varying(255),
    capability_id integer NOT NULL,
    permission integer DEFAULT 0,
    event_time timestamp without time zone,
    event_type character varying(10))
  SERVER myserver;
  GRANT ALL ON TABLE next_gen_permissions_change TO foo;
  </code></pre>
  
  <p>When I INSERT rows into source.next_gen_permissions_change, I see them show up in audit.next_gen_permissions_change...so I got that going for me. </p>
  
  <p>Here's the function: </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION audit_permissions()
    RETURNS trigger AS
  $BODY$
  DECLARE
      trigger_event_time timestamp;
  BEGIN
      trigger_event_time := now();
  
      IF (TG_OP = 'INSERT') THEN
      INSERT INTO next_gen_permissions_change(
              id, 
              authorizable_type, 
              authorizable_id, 
              grantee_id, 
              grantee_type, 
              capability_id, 
              permission, 
              event_time, 
              event_type)
          VALUES (NEW.id,
              NEW.authorizable_type, 
              NEW.authorizable_id, 
              NEW.grantee_id, 
              NEW.grantee_type, 
              NEW.capability_id, 
              NEW.permission, 
              trigger_event_time, 
              'insert');
          RETURN NEW;
      ELSIF (TG_OP = 'DELETE') THEN
              INSERT INTO next_gen_permissions_change(
              id, 
              authorizable_type, 
              authorizable_id, 
              grantee_id, 
              grantee_type, 
              capability_id, 
              permission, 
              event_time, 
              event_type)
          VALUES (OLD.id,
              OLD.authorizable_type, 
              OLD.authorizable_id, 
              OLD.grantee_id, 
              OLD.grantee_type, 
              OLD.capability_id, 
              OLD.permission, 
              trigger_event_time, 
              'delete');
              RETURN OLD;
      END IF;
  
      RETURN null;
  END;
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
  </code></pre>
  
  <p>And now, I'm adding the function as a trigger: </p>
  
  <pre><code>CREATE TRIGGER next_gen_permissions_audit
  AFTER INSERT OR DELETE
  ON next_gen_permissions
  FOR EACH ROW
  EXECUTE PROCEDURE audit_permissions();
  </code></pre>
  
  <p>No errors, all seems well: </p>
  
  <p><a href="https://i.stack.imgur.com/pPTJe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pPTJe.png" alt="enter image description here"></a></p>
  
  <p>As you can see, I added RAISE NOTICE as well, so I could hopefully see this sucker executing in pgadmin. </p>
  
  <p>Here are all of the properties of the trigger (parent window) and function (child window embedded inside the parent):</p>
  
  <p><a href="https://i.stack.imgur.com/qvX1r.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qvX1r.png" alt="enter image description here"></a></p>
  
  <p>WHEN executing something like </p>
  
  <pre><code>UPDATE next_gen_permissions SET f1=f1
  </code></pre>
  
  <p>...I don't see anything in the messages. </p>
  
  <p>Also, </p>
  
  <pre><code>EXPLAIN ANALYZE UPDATE next_gen_permissions SET f1=f1
  </code></pre>
  
  <p>doesn't return anything interesting: </p>
  
  <p><a href="https://i.stack.imgur.com/dqCou.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dqCou.png" alt="enter image description here"></a></p>
  
  <p>Can anyone see what I'm doing wrong here? I'm not getting any error messages, and when I look at the logs, the UPDATE goes right through without ANY mention that the trigger is getting fired. </p>
  
  <p>Thanks much. </p>