public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matt Magoffin <postgresql.org@msqr.us>
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: Thu, 30 Apr 2026 21:35:43 +0200
Message-ID: <83819e975523be4d320141ff1363dfb40c82289b.camel@cybertec.at> (raw)
In-Reply-To: <93ADF4E5-CF49-4347-8A79-33655A8E0299@msqr.us>
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>
	<93ADF4E5-CF49-4347-8A79-33655A8E0299@msqr.us>

On Fri, 2026-05-01 at 07:07 +1200, Matt Magoffin wrote:
> > 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.

Also, the behavior difference only occurs with ON CONFLICT DO NOTHING.
If you use ON CONFLICT ... DO UPDATE ..., the update will block.
That makes the behavior difference somewhat less bad in my eyes.

Yours,
Laurenz Albe

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: laurenz.albe@cybertec.at, postgresql.org@msqr.us, 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: <83819e975523be4d320141ff1363dfb40c82289b.camel@cybertec.at>

* 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