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
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.