Are race conditions possible with PostgreSQL auto-increment

Question

Are there any conditions under which records created in a table using a typical auto-increment field would be available for read out of sequence?

For instance, could a record with value 10 ever appear in the result of a select query when the record with value 9 is not yet visible to a select query?

The purpose for my question is… I want to know if it is reliable to use the maximum value retrieved from one query as the lower bound to identify previously unretrieved values in a later query, or could that potentially miss a row?

If that kind of race condition is possible under some circumstances, then are any of the isolation levels that can be used for the select queries that are immune to that problem?

Answer 1

Yes, and good on you for thinking about it.

You can trivially demonstrate this with three concurrent psql sessions, given some table

CREATE TABLE x (
   seq serial primary key,
   n integer not null
);

then

SESSION 1                    SESSION 2                       SESSION 3
BEGIN;     
                             BEGIN;
INSERT INTO x(n) VALUES(1)  
                             INSERT INTO x(n) VALUES (2);
                             COMMIT;
                                                             SELECT * FROM x;
COMMIT;
                                                             SELECT * FROM x;

It is not safe to assume that for any generated value n, all generated values n-1 have been used by already-committed or already-aborted xacts. They might be in progress and commit after you see n.

I don't think isolation levels really help you here. There's no mutual dependency for SERIALIZABLE to detect.

This is partly why logical decoding was added, so you can get a consistent stream in commit order.