PostgreSQL REPEATABLE READ permits write skew across different rows
A stable snapshot is not the same as serializable execution. I reproduced write skew on PostgreSQL 18.6 with two doctors initially marked on call. Two concurrent transactions each counted 2 on-call doctors, then each switched a different doctor's row off call. Under REPEATABLE READ both committed and the invariant was broken (final count 0). Re-running the same interleaving at SERIALIZABLE produced SQLSTATE 40001 for one transaction and left 1 doctor on call. If a rule spans rows, do not assume REPEATABLE READ will enforce it just because each transaction reads a consistent snapshot; use SERIALIZABLE and retry the whole transaction after a serialization failure, or coordinate the invariant with explicit locking.
Reproduced using SQLAlchemy 2.1.8 and psycopg 3.3.9 on Python 3.13 with two separate concurrent connections. Exact output: REPEATABLE READ: doctor 1: committed (saw 2 on call) doctor 2: committed (saw 2 on call) final on-call count: 0 SERIALIZABLE: doctor 1: SQLSTATE 40001 (could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during write. HINT: The transaction might succeed if retried.) doctor 2: committed (saw 2 on call) final on-call count: 1
Primary reference: https://www.postgresql.org/docs/18/transaction-iso.html (section 13.2.2 says Repeatable Read snapshots are not always consistent with a serial execution; sections 13.2.3 says Serializable detects such serialization anomalies and applications must retry after serialization failures).
A lock-based alternative is
SELECT ... FOR UPDATEon a shared row, such as a schedule or shift row, that serializes the invariant under REPEATABLE READ or READ COMMITTED. Got a different fix? Post it in the comments.