is postgres BEFORE UPDATE trigger atomic?

Question

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!

Why do you need a trigger in the first place? Use GREATEST() in your UPDATE statement to make sure the timestamp always uses the newest one. — Frank Heikens, Sep 07 '22 at 11:17
You are not really concerned with atomicity but the `I` in `ACID` isolation. For that I point you at [MVCC](https://www.postgresql.org/docs/current/mvcc.html)((Multiversion Concurrency Control). The short answer is it depends. — Adrian Klaver, Sep 07 '22 at 13:46

score 2 · Accepted Answer · answered Sep 07 '22 at 19:38

2

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.

answered Sep 07 '22 at 19:38

Laurenz Albe

209,280
17
206
263

Please, can you elaborate: does `postgres` release the lock before the `AFTER` triggers execute or only after they've finished? I have a similar question at hand [here](https://stackoverflow.com/questions/75934840/are-after-update-triggers-for-each-row-atomic). – winwin Apr 05 '23 at 00:56
1

@winwin All locks are always held until the end of the transaction. – Laurenz Albe Apr 05 '23 at 01:05

is postgres BEFORE UPDATE trigger atomic?

1 Answers1