IF condition evaluation in AFTER TRIGGER function on PostgreSQL

Viewed 51

I'm writing a plpgsql function in PostgreSQL (version 10) which is called by a TRIGGER after an UPDATE

  CREATE TRIGGER trigger_1
   AFTER UPDATE
   ON entities
   FOR EACH ROW
   WHEN ( condition_on_new_and_old )
   EXECUTE PROCEDURE function_1();

entities is a table which has a column data of type JSONB.

The code of function_1 is essentially (I modified the code to isolate the RAISE in the IF, originally the condition was the inverse):

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

As everyone can expect if the condition in the IF is true we raise the exception. The problem is the RAISE is always executed despite the value of the condition. In all my tests the values of OLD and NEW have always been the same.

Even more surprising was the fact that if I did something like

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  RAISE EXCEPTION 'TEST'

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

I had the exception 'TEST' raised but if I wrote:

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  -- RAISE EXCEPTION 'TEST' (I commented the RAISE)

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

I had the exception 'XXX not found' thrown.

To notice the trigger used to be on BEFORE UPDATE and it did work as expected, the problem arrived when we set it to AFTER.

I'm quite sure I'm missing something about how AFTER triggers behave. Do you have any ideas?

Thank you all in advance

1 Answers

Thanks for the comments on the question. I'm sorry but I didn't notice in the logs that there was actually a problem in the WHEN condition of the TRIGGER.

After fixing it it's working.

Related