PostgreSQL – How to Get Transaction Commit Timestamp

postgresqltimestamp

I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way:

SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp
_some_persisted_timestamp = CURRENT_TIMESTAMP
Processing data received from step 1
Sleep for 5s
Go to step 1

Where modified_timestamp updated with trigger (after any row update modified_timestamp becomes CURRENT_TIMESTAMP).
It worked fine until I noticed that CURRENT_TIMESTAMP in Postgres is transaction start timestamp in fact and some of the updates are lost. Why are they lost? That's quite simple – at the moment when I execute query SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp some of the changes have already occurred, but modified_timestamp is before updated _some_persisted_timestamp because transaction is in progress still.

This problem could be easily solved by assigning in step 2 timestamp when update becomes visible for other transactions (transaction commit timestamp in other words) instead of CURRENT_TIMESTAMP or clock_timestamp().

I read documentation, but have found nothing related to transaction commit timestamp. Could you kindly suggest smth?

Btw, I'm aware of logical decoding and I know that this mechanism suits better for my needs in theory, but there are certain practical problems not allowing me to use it.

Best Answer

This problem could be easily solved by assigning in step 2 timestamp when update becomes visible for other transactions (transaction commit timestamp in other words) instead of CURRENT_TIMESTAMP or clock_timestamp().

This is logically impossible. Postgres writes new row versions before it finally commits to make them visible. It would require prophetic capabilities to write a future timestamp yet unknown at the time of writing.

However, you can get commit timestamps from a different source: since Postgres 9.5, there is a GUC setting track_commit_timestamp to start logging commit timestamps globally.

Then you can get commit timestamps with the utility function pg_xact_commit_timestamp(xid). Your query could look like:

SELECT * FROM my_table t
WHERE   pg_xact_commit_timestamp(t.xmin) > _some_persisted_timestamp;

Be aware that commit timestamps are not kept around forever. After two billion transactions transactions (2^31), transaction IDs are "frozen". That does not delete it right away, but after 4 billion transactions, the information is gone for certain. That's a big number of transactions, and only very busy databases burn that much over a lifetime. But there can be programming errors burning through transaction numbers more quickly than expected ...

Your step 2 and step 3 trade positions, and you record the commit timestamp instead of CURRENT_TIMESTAMP - or xmin from any freshly updated row to derive the commit timestamp with pg_xact_commit_timestamp() once more.

About xmin:

How to view tuples changed in a PostgreSQL transaction?

But I am not completely sure I understand your task. Maybe you need a queuing tool or process rows one by one like discussed here:

Postgres UPDATE ... LIMIT 1

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;

PostgreSQL – How to Get the Current Unix Timestamp

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

PostgreSQL – How to Get the Current Unix Timestamp

Related Question