0

I've created simple trigger

CREATE TABLE data (
    id int,
    v1 text,
    v2 text
);

CREATE TABLE events (
    id serial primary key,
    v1 text
);

CREATE OR REPLACE FUNCTION emit_event_for_v1()
RETURNS trigger AS '
    BEGIN
        IF OLD.v1 != NEW.v1 THEN
            INSERT INTO events (v1)
            VALUES (NEW.v1);
        END IF;
        RETURN NULL;
    END
' LANGUAGE 'plpgsql';

CREATE TRIGGER event_v1 AFTER INSERT OR UPDATE ON data
FOR EACH ROW EXECUTE PROCEDURE emit_event_for_v1();

INSERT INTO data VALUES (1, '', '');

After that, I've launched many clients (25) doing many updates (500 each) on the data.

UPDATE data SET v1 = $1 WHERE id = 1

with $1 being $WORKER_ID : $ITER_ID. In the end I've verified that last inserted event (SELECT id, v1 FROM events ORDER BY id DESC) matches current state of data (SELECT v1 FROM data WHERE id = 1).

I've also checked that id of last event is equal to number of updates (25 * 500). And it is.

My question is, is this guaranteed to work? Are results of inserts in triggers always visible in same order as the original update that triggered the trigger? Or did I just get lucky during my testing (like 50 times in a row)?

4
  • Trigger executes in the same transaction as the DML statement that activates it, so yes, he latest event (as identified by the largest serial ID, barring wraparound) will be from the latest (by the wall clock) update to data.
    – mustaccio
    Mar 27, 2020 at 0:56
  • Not directly related to your question, but you probably want to use if OLD.v1 is disting from NEW.v1 to properly deal with NULL values
    – user1822
    Mar 27, 2020 at 6:11
  • @a_horse_with_no_name good point, thanks :)
    – graywolf
    Mar 27, 2020 at 7:50
  • @mustaccio would you please write that as an answer so I can accept it? I think just copy&paste would suffice.
    – graywolf
    Mar 27, 2020 at 7:50

1 Answer 1

0

Yes, that is guaranteed, because there is no chance that the trigger function for two updates of the same row will be executed at concurrently.

The UPDATE will get an exclusive lock on the row, and the trigger function will run after the update. The transaction (that holds the exclusive lock) will not complete until the trigger has run, so no concurrent UPDATE can get the lock and start running until the previous one is done.

An exception would be if you had defined the sequence with the CACHE clause, so that backends can cache sequence values, but you didn't do that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.