Are PostgreSQL auto-race conditions possible

Question

Are there any conditions under which records created in a table using a typical auto-increment field can be read from a sequence?

For example, can a record with a value of 10 ever appear as a result of a select query when a record with a value of 9 is not yet visible to the select query?

The purpose of my question is: ... I want to know whether it is reliable to use the maximum value obtained from one query as a lower bound for determining previously invalid values ​​in a later query or can this potentially skip a string?

If such a race condition is possible under certain circumstances, is there any isolation level that can be used for selected queries that are immune to this problem?

Answer 1

Yes, and it’s good if you think about it.

You can trivially demonstrate this with three simultaneous 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 unsafe to assume that for any generated value, nall generated values ​​were n-1used by already processed or already canceled xacts. They can be performed and performed after you see n.

, . SERIALIZABLE .

, , .