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 1
.
However, when updating the same row 1
, updated_at
should only grow.
So, when we have an update we want to:
- Upsert into
data
table. - Insert into
data_history
table. - Make sure that new
updated_at
is 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;
?