Atomically set SERIAL value when committing transaction

Question

Say I have a table where I want to use a serial as primary key to ask for changes from the client. The client will ask "give me the changes after key X". Without using SERIALIZABLE isolation level or locking, this is prone to race conditions.

Transaction A can start first, and do its writes, then take a long time to commit. Meanwhile transaction B will start and commit, before A commits. The write from B will get a higher primary key than the write from A. If a client now asks for changes it will miss the still uncommitted write from A, and note the newest highest primary key. So even after A commits, the client will never see that change, because its key is lower than the change the client already got.

Is it possible to make the value of a serial (or similar from a counter) be determined atomically at commit time so that we are guaranteed that it will be higher than all others when committed, and lower than all which will be committed after it? If not what is the best way to solve this problem?

Answer 1

Postgres 9.5 introduced a new feature related to this problem: commit timestamps.

You just need to activate track_commit_timestamp in postgresql.conf (and restart!) to start tracking commit timestamps. Then you can query:

SELECT * FROM tbl
WHERE  pg_xact_commit_timestamp(xmin) >= '2015-11-26 18:00:00+01';

Read the chapter "Commit timestamp tracking" in the Postgres Wiki.
Related utility functions in the manual.

Function volatility is only VOLATILE because transaction IDs (xid) can wrap around per definition. So you cannot create a functional index on it.
You could fake IMMUTABLE volatility in a function wrapper for applications in a limited time frame, but you need to be aware of implications. Related case with more explanation:

• Does PostgreSQL support "accent insensitive" collations?
• How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

For many use cases (like yours?) that are only interested in the sequence of commits (and not absolute time) it might be more efficient to work with xmin cast to bigint "directly" (xmin::text::bigint) instead of commit timestamps. (xid is an unsigned integer internally, the upper half that does not fit into a signed integer.) Again, be aware of limitations due to possible xid wraparound.

For the same reason, commit timestamps are not preserved indefinitely. For small to medium databases, xid wraparound hardly ever happens - but it will eventually if the cluster is live for long enough. Read the chapter "Preventing Transaction ID Wraparound Failures" in the manual for details.