1

I want to create sync API in which users can get the changes since their last sync request, the API exposes cursor-based pagination (sync token)
A possible cursor may be to use updatedAt column along with xmin column (I need the xmin because many rows may have the same timestamp).

My only concern is if it's possible to have rows with same (updatedAt) timestamp and wrapped around xmin:

+---------------------+------------+
| timestamp           | xmin       |
+---------------------+------------+
| 2011-01-01 00:00:00 | 4556455456 | -> first row
+---------------------+------------+
| 2011-01-01 00:00:00 | 6          | -> second row, wrapped around xid
+---------------------+------------+

Within a specific timestamp (moment), I can be sure xmin will behave like a simple sequence, right?

Improve this question

1 Answer 1

Reset to default
2

Assuming updatedAt stores the transaction timestamp as reported by now() or CURRENT_TIMESTAMP. See:

Then yes, xmin behaves like a simple sequence, basically.

But you may want to look at cmin additionally. The manual:

The command identifier (starting at zero) within the inserting transaction.

Related:

And, subtransactions (incurring a SAVEPOINT) may very well interfere with your plans, as those advance the transaction ID (and hence the xmin of rows written after that) within the same transaction, while the transaction timestamp stays the same.

So, that's a YES to the question in the title. It is possible to have xid before and after wrap around with the same timestamp.

There are various ways subtransactions may get involved:

  • Calling the SQL commands manually, obviously
  • A PL/pgSQL block with an EXCEPTION clause
  • The ON_ERROR_ROLLBACK setting in Postgres
  • Some replication solutions involve savepoints (I think)

Related:

Improve this answer
2
  • Thanks. just to be sure, so I can't use xmin for sync, right? another solution I think about is to save the txid_current value (on every row) which doesn't suffer the wraparound problem and its value only grows. it also should solve the subtransactions problem, am I correct?
    – Michael
    Jun 20, 2020 at 10:52
  • about cmin what if two transactions start within the same timestamp? I'll have 2 rows with the same timestamp and both cmin=0, isn't?
    – Michael
    Jun 21, 2020 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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