Re: Write skew observed under serializable isolation

Let me give a similar example that also makes the final sum query incorrect
under serializable isolation.

Same setup:

CREATE TABLE trs (id int, v int);
INSERT INTO trs VALUES (1, 0), (2, 0);

Session 1:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0

Session 2:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0

-- session 1
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,1)';

-- session 2
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
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

Neither serial order produces 2; both produce 3. The serial run is shown at
the end of this mail.

The range read is a Tid Range Scan standalone and as the InitPlan of the
UPDATE:

Update on trs
InitPlan expr_1
-> Aggregate
-> Tid Range Scan on trs trs_1
TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid <=
'(0,100)'::tid))
-> Tid Scan on trs
TID Cond: (ctid = '(0,1)'::tid)

As a control, force the same reads through a sequential scan by setting

SET enable_tidscan = off;

in both sessions instead of enable_seqscan. (Note that merely leaving
enable_seqscan on is not enough: the planner still prefers a Tid Range Scan
for this qual, so a control written that way is not a control.) The
identical
schedule then fails at the second commit with

ERROR: could not serialize access due to read/write dependencies among
transactions

and leaves sum = 1. A Tid Scan control aborts correctly as well, since
heap_fetch() takes PredicateLockTID on each visible tuple it returns.

The cause appears to be the scan-type test in heap_beginscan(), in
src/backend/access/heap/heapam.c:

if (scan->rs_base.rs_flags & (SO_TYPE_SEQSCAN | SO_TYPE_SAMPLESCAN))
{
Assert(snapshot);
PredicateLockRelation(relation, snapshot);
}

SO_TYPE_TIDRANGESCAN is not in the list, so a TID range scan takes no SIREAD
lock at all. The comment just above already describes the rule that covers
this case -- "in a heap scan there is nothing more fine-grained to lock" --
and a TID range scan is a heap scan with no index involved. The conflict-out
edge is still recorded by heap_prepare_pagescan(); what is missing is the
conflict-in edge, so a later writer's CheckForSerializableConflictIn() finds
no read to conflict with and no cycle can be detected.

As mentioned previously, I reproduced this on current master. Tid Range
Scans were introduced in
PostgreSQL 14, the test above is unchanged on master, and the behavior
appears
present in all branches since then.

Finally, for reference, here are the same statements run serially --
session 1
start to finish, then session 2 -- which is what a serializable execution is
required to be equivalent to. Starting again from the setup above:

-- session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,1)';
COMMIT;

-- session 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 1 <-- sees session 1's write, so it stores 2 rather than 1
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,2)';
COMMIT;

SELECT sum(v) FROM trs;
-- 3

Running session 2 first gives 3 as well, by symmetry. The interleaved result
of 2 is therefore not equivalent to any serial order.

Best,
Jacob Brazeal

On Sun, Jul 26, 2026 at 11:05 PM Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com>
wrote:

> A candidate patch to fix is attached.
>