We have a table for which we need to maintain a log of updates, a history. We are currently using timestamp as a way to order these updates. Pretty much, two tables, the only difference is the primary key (
data table only keeps the latest,
data_history keeps all of them):
CREATE TABLE data( id STRING, updated_at TIMESTAMPTZ NOT NULL, data STRING, PRIMARY KEY(id) ); CREATE TABLE data_history( id STRING, updated_at TIMESTAMPTZ NOT NULL, data STRING, PRIMARY KEY(id, updated_at) );
The constraint we have is that each next update should have a timestamp that is greater than the previous one, so we need a monotonically increasing timestamp.
Notice that we only need that guarantee per each row, not across all the rows. It’s okay if last update to row with id
2 will have smaller timestamp than last update to row with id
1 even though update to
2 happened later than update to
However, when updating the same row
updated_at should only grow.
So, when we have an update we want to:
- Upsert into
- Insert into
- Make sure that new
updated_atis strictly greater than the previous value.
My understanding was that
statement_timestamp() is not guaranteed to be “monotonically increasing”, so I ended up doing the following query to enforce that it is monotonic:
INSERT INTO data(id, updated_at, data) VALUES ('1', statement_timestamp(), 'hello') ON CONFLICT (id) DO UPDATE SET updated_at = GREATEST(excluded.updated_at, data.updated_at + '1 microsecond'::interval), data = excluded.data RETURNING updated_at;
However, after reading the last blog post, it seems to me that it would not be necessary? If
statement_timestamp() will ever happen to be the less than the previous one, that would mean my current transaction timestamp is smaller than the value timestamp and it falls into this “uncertainty” scenario and transaction is restarted.
Am I correct? Can it be replaced with
INSERT INTO data(id, updated_at, data) VALUES ('1', statement_timestamp(), 'hello') ON CONFLICT (id) DO UPDATE SET data = excluded.data RETURNING updated_at;