Hi hackers, I found that a query using a Tid Range Scan can allow write skew under `SERIALIZABLE`.
Setup: CREATE TABLE trs (id int, v int); INSERT INTO trs VALUES (1, 0), (2, 0); Then run the following in two sessions, interleaved as shown. Session 1: BEGIN ISOLATION LEVEL SERIALIZABLE; SET enable_seqscan = off; SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)'; -- 0 -- implies session 2 has not committed yet Session 2: BEGIN ISOLATION LEVEL SERIALIZABLE; SET enable_seqscan = off; SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)'; -- 0 -- implies session 1 has not committed yet EXPLAIN shows a Tid Range Scan for both reads. Now: -- session 1 UPDATE trs SET v = 1 WHERE ctid = '(0,1)'; -- session 2 UPDATE trs SET v = 1 WHERE ctid = '(0,2)'; -- session 1 COMMIT; -- session 2 COMMIT; -- succeeds Both transactions commit, and the final sum is 2: SELECT sum(v) FROM trs; -- 2 This result is not possible in any serial order: since both transactions read the full range, whichever transaction ran second would have observed a sum of 1 in its initial select instead of 0. As a control, the same schedule using a sequential scan causes the second commit to fail with: ERROR: could not serialize access due to read/write dependencies among transactions (To run with seqscan, make sure to first do both SET enable_tidscan = off and SET enable_seqscan = on.) I reproduced this on current master. Tid Range Scans were introduced in PostgreSQL 14, and the behavior appears present in all branches since then. Best, Jacob Brazeal
