Hi,
On 2026-09-11 12:49:54 -0400, Andres Freund wrote:
> I wonder if there may be additional issues with DELETE ... FOR PORTION OF, due
> to not having a ctid chain to follow.
Yep. There's lost updates even with full-key locks, once DELETE FPO enters the
picture.
See the AI generated spec file (although I really needed to force both Opus 5
and Fable 5.1 to get to it, they both swore up and down that this isn't a real
issue at first).
The problem is that with UPDATE FPO different backends serialize on the
surviving row, allowing only one backend to acquire the FOR UPDATE lock on
that row, with the other transaction waiting for the second transaction to
either abort, or to lock the subsequent row.
But with DELETE FPO, there's no such serialization, once the first transaction
commits all concurrent FOR UPDATEs complete, *without* needing a row lock.
So I think either FPO needs a fair bit more work (e.g. using the speculative
insert infrastructure from ON CONFLICT and/or perhaps some careful scanning
with a dirty snapshot), or the feature ought to just refuse to be used with
READ COMMITTED. I'm a bit sceptical that the latter is acceptable. And the
former seems very clearly out of scope for 19.
Greetings,
Andres Freund
# Whole-key SELECT FOR UPDATE does not serialize temporal DELETE leftovers.
# All writers lock every row for the key in the same transaction as their DML.
#
# s1 deletes the original tuple and inserts [1,10) as an independent leftover.
# s2's locking scan waits for s1, then skips the deleted tuple. Its snapshot
# cannot see the leftover, so it finishes without locking any live row.
# s3 can therefore lock the whole key and update [2,3). s2's subsequent UPDATE
# sees [1,10), but waits for s3 and follows its update chain only to [2,3).
# That tuple fails s2's overlap qual; s3's [3,10) leftover is invisible to s2's
# UPDATE snapshot.
#
# FIXME: s2 changes no rows and leaves [4,5) at price 0, rather than price 2.
# The three disjoint changes should all survive, regardless of serial order.
setup
{
DROP TABLE IF EXISTS fpo_lockall_delete_gap;
CREATE TABLE fpo_lockall_delete_gap (
id int4range NOT NULL,
valid_at int4range NOT NULL,
price integer NOT NULL,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
INSERT INTO fpo_lockall_delete_gap VALUES ('[1,2)', '[0,10)', 0);
}
session s1
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s1lock {
SELECT * FROM fpo_lockall_delete_gap WHERE id = '[1,2)' FOR UPDATE;
}
step s1delete {
DELETE FROM fpo_lockall_delete_gap
FOR PORTION OF valid_at FROM 0 TO 1
WHERE id = '[1,2)'
RETURNING *;
}
step s1commit { COMMIT; }
step result {
SELECT * FROM fpo_lockall_delete_gap ORDER BY id, valid_at;
}
session s2
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s2lock {
SELECT * FROM fpo_lockall_delete_gap WHERE id = '[1,2)' FOR UPDATE;
}
step s2update {
UPDATE fpo_lockall_delete_gap
FOR PORTION OF valid_at FROM 4 TO 5
SET price = 2 WHERE id = '[1,2)'
RETURNING *;
}
step s2commit { COMMIT; }
session s3
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s3lock {
SELECT * FROM fpo_lockall_delete_gap WHERE id = '[1,2)' FOR UPDATE;
}
step s3update {
UPDATE fpo_lockall_delete_gap
FOR PORTION OF valid_at FROM 2 TO 3
SET price = 3 WHERE id = '[1,2)'
RETURNING *;
}
step s3commit { COMMIT; }
permutation s1lock s1delete s2lock s1commit s3lock s3update s2update s3commit
s2commit result
permutation s1lock s1delete s2lock s3lock s1commit s3update s2update s3commit
s2commit result
