I am trying to both timestamp and version rows in a table as they are updated. I have the following tigger:
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
NEW.version = OLD.version + 1;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Which I add to my table like this:
CREATE TRIGGER exchange_order_trigger_1 BEFORE UPDATE ON exchange_order
FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
I have a second trigger that uses PERFORM pg_notify('table_watch', textPayload); to notify my nodejs app of each change by sending it a the row as a json string. The nodejs app just logs each row to the console.
I thought it was working perfectly, but I noticed that the timestamps and version number do not increase in step. Here is an example of the output (in the order they were received):
"updated_at":"2020-07-16T06:43:40.234227+00:00","version":1
"updated_at":"2020-07-16T06:43:41.170334+00:00","version":2
"updated_at":"2020-07-16T06:43:50.930157+00:00","version":3
"updated_at":"2020-07-16T06:43:46.528429+00:00","version":4
"updated_at":"2020-07-16T06:43:51.717432+00:00","version":5
"updated_at":"2020-07-16T06:43:48.300129+00:00","version":6
"updated_at":"2020-07-16T06:43:53.103254+00:00","version":7
As you can see, the version numbers increase as expected, but the timestamps do not. The version 4 timestamp is earlier than the version 3 timestamp for example.
When I look at the other data in the rows, it looks like the updated_at value is the correct, and the version is wrong.
What have I done wrong? Any help greatly appreciated.
Using a google cloud platform hosted instance of Postgres 9.6