3

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:

  1. SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp
  2. _some_persisted_timestamp = CURRENT_TIMESTAMP
  3. Processing data received from step 1
  4. Sleep for 5s
  5. 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.

  • You are linking to the Postgres 9.4 manual. Doesn't mean you are on 9.4, does it? (Would be a dealbreaker.) – Erwin Brandstetter Mar 15 at 18:49
  • @ErwinBrandstetter I'm on 9.5.4 and can update if required version is available in Amazon RDS – bsiamionau Mar 18 at 11:48
  • 1
    Upgrade at least to the latest point release. 9.5.16 at the time of writing. See recommendations here. – Erwin Brandstetter Mar 18 at 13:46
  • @ErwinBrandstetter I had no chance to try suggested approach yet, I'll immediately report after I have any results, thank you! – bsiamionau Apr 1 at 7:09
6

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;

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.

More:

About xmin:

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:

  • Thank you so much for detailed response! To understand more about my task: docs.confluent.io/current/connect/kafka-connect-jdbc/… Problem occurs while using timestamp mode - connector works exactly the same way as I described in question. And it loses updates. Solution you suggested looks awesome - need to test it first before accepting as right answer. Thanks again! – bsiamionau Mar 18 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.