Re: FOR PORTION OF code review

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