Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

public inbox for pgsql-general@postgresql.org  
help / color / mirror / Atom feed

From: Matt Magoffin <postgresql.org@msqr.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Fri, 1 May 2026 07:07:30 +1200
Message-ID: <93ADF4E5-CF49-4347-8A79-33655A8E0299@msqr.us> (raw)
In-Reply-To: <bd0599f050972d15202ba30ab872972a050ba8e0.camel@cybertec.at>
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
	<ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
	<8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
	<bd0599f050972d15202ba30ab872972a050ba8e0.camel@cybertec.at>


> On 30 Apr 2026, at 6:42 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 
> 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.

Thank you for your additional insights, Laurenz.

Kind regards,
Matt

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: postgresql.org@msqr.us, laurenz.albe@cybertec.at, adrian.klaver@aklaver.com, pgsql-general@lists.postgresql.org
  Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
  In-Reply-To: <93ADF4E5-CF49-4347-8A79-33655A8E0299@msqr.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox