using triggers to sequence and timestamp updates in Postgres

141 Views Asked by At

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

0

There are 0 best solutions below