is postgres BEFORE UPDATE trigger atomic?-CodePudding

I trying to do conditional UPDATE trigger. It should update row only if certain condition, depending on old row data, is met (I know I can simply do UPDATE ... WHERE ... but from various reasons trigger is the way to go for us):

CREATE
OR REPLACE FUNCTION update_only_with_higher_timestamp()
  RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
 IF
NEW.timestamp < OLD.timestamp THEN
    RETURN OLD;
END IF;

RETURN NEW;
END;$$;


CREATE TRIGGER avoid_updating_rows_with_lower_timestamp
    BEFORE UPDATE
    ON table
    FOR EACH ROW
    EXECUTE PROCEDURE update_only_with_higher_timestamp();

My question is, is this operation atomic? Or could it happen that I base that condition on old data which might not be the most up to date at the time?

Thanks for the answer!

CodePudding user response：

That trigger is safe. PostgreSQL takes the row lock before it executes the BEFORE trigger, so the row is already locked against concurrent data modifications.

You can optimize the trigger function considerably if you RETURN NULL rather than RETURN OLD if you don't want the UPDATE to happen. Returning NULL will abort the operation (without an error), while returning OLD will perform an UPDATE that has no effect, but costs performance.