On Thu, 2026-04-30 at 14:48 +1200, Matt Magoffin wrote: > > On 30 Apr 2026, at 11:37 AM, Adrian Klaver <[email protected]> > > wrote: > > > > So in your first case the INSERT is never done and there is no lock for the > > INSERT in any case. > > Thanks for the info, Adrian. And so for my 2nd case, where the INSERT is > blocked by the > DELETE statement, I see the docs say > > The FOR UPDATE lock mode is also acquired by any DELETE on a row… > > But I am not finding the info that talks about why the INSERT … ON CONFLICT > DO NOTHING does > block until the DELETE finishes. I guess in my mind the SELECT … FOR UPDATE > and DELETE were > acquiring the same kind of row lock, so the behaviour of the INSERT would be > the same across both cases. > > I suppose what I’d be keen to confirm is that the blocking behaviour I get > with the DELETE is > expected behaviour, that I can count on. Do you know if that is true?
I admit that the behavior difference surprised me too. I tried to spot the difference, and using the pageinspect extension I see the following: - after the DELETE, "infomask" is set to 0x0100 - after the SELECT ... FOR UPDATE, "infomask" is set to 0x01c0 Now 0x0100 is HEAP_XMIN_COMMITTED, a hint bit. The difference is that in the SELECT ... FOR UPDATE case, there are also HEAP_XMAX_EXCL_LOCK and HEAP_XMAX_LOCK_ONLY set, which means that "xmax" stores an exclusive row lock. In other words, after the DELETE, there is *no* row lock on the row. "xmax" stores the transaction ID of the transaction that deleted the row - only that transaction is still active, and its effects not yet visible. So I'd say that the documentation is not quite accurate. Really, the DELETE does not place a row lock on the row. That must account for the behavior difference: after the SELECT ... FOR UPDATE, the INSERT ... ON CONFLICT interprets the row lock as a conflict and moves on, while in the DELETE case it sees no conflict (yet), but has to wait for the transaction to complete before it knows how to proceed. I cannot say if that is intentional; as I said initially, I am surprised too. Yours, Laurenz Albe
