public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Matt Magoffin <postgresql.org@msqr.us>
To: 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 14:48:32 +1200
Message-ID: <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us> (raw)
In-Reply-To: <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
	<ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>


> On 30 Apr 2026, at 11:37 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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?

Cheers,
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, 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: <8BCF50C4-D36B-4453-9B09-AA717AE6F563@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