Atomic value of SERIAL during transaction

Question

Let's say I have a table where I want to use serial as the primary key to request changes from the client. The client will ask "give me changes after key X". Without using an isolation level or SERIALIZABLE lock SERIALIZABLE is subject to race conditions.

Transaction A can start over and take notes and then take a long time. Meanwhile, transaction B will begin and complete before it completes A. An entry from B will receive a higher primary key than an entry from A. If the client now asks for changes, it will skip the still uncommitted entry from A and mark the most recent primary key. Thus, even after A completes the transaction, the client will never see this change, because his key is lower than the client has already received.

Is it possible that the value of a serial (or similar from the counter) was determined atomically during the fixation, so that we were sure that it would be higher than all the others when committed and lower than everything that would be perfect after it? If this is not the best way to solve this problem?

Answer 1

Postgres 9.5 introduced a new feature related to this issue: timestamping .

You just need to activate track_commit_timestamp in postgresql.conf (and restart!) In order to start tracking commit commit marks. Then you can request:

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

Read the “Lock Timestamp” chapter on the Wiki Postgres.

Related utility functions in the manual .

Function volatility is only VOLATILE , because transaction identifiers ( xid ) can wrap around each definition. Thus, you cannot create a functional index on it.
You could fake IMMUTABLE volatility in the shell of functions for applications for a limited period of time, but you need to know the consequences. A related case with a lot of explanations:

• Does PostgreSQL support "accent insensitive" collation?
• How do IMTUTABLE, STABLE and VOLATILE functions affect the actions?

For many use cases (like yours?) That are only interested in the sequence of commits (and not the absolute time), it would be more efficient to work with xmin directly on bigint "( xmin::text::bigint ) instead of fixing timestamps.
_{(Now offering bigint instead, if integer : xid is an unsigned integer that doesn't fit in the signed integer in the upper half.)}
Again, be aware of the limitations due to a possible xid bypass. Read on ...

For the same reason, commit marks are not stored indefinitely . For small and medium sized databases, xid wraparound almost never happens - but it will end up if the cluster lives long enough. For more information, see the Transaction Failure Prevention chapter in the manual.