From: Matt Magoffin <postgresql.org@msqr.us>
Message-Id: <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_A528BCC8-165A-45C7-85E9-621E633890D6"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.6\))
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO
 NOTHING
Date: Thu, 30 Apr 2026 14:48:32 +1200
In-Reply-To: <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
To: Adrian Klaver <adrian.klaver@aklaver.com>
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
 <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/8BCF50C4-D36B-4453-9B09-AA717AE6F563%40msqr.us>
Precedence: bulk


--Apple-Mail=_A528BCC8-165A-45C7-85E9-621E633890D6
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On 30 Apr 2026, at 11:37=E2=80=AFAM, Adrian Klaver =
<adrian.klaver@aklaver.com> wrote:
>=20
> 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=E2=80=A6

But I am not finding the info that talks about why the INSERT =E2=80=A6 =
ON CONFLICT DO NOTHING does block until the DELETE finishes. I guess in =
my mind the SELECT =E2=80=A6 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=E2=80=99d 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=

--Apple-Mail=_A528BCC8-165A-45C7-85E9-621E633890D6
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;"><br><div><blockquote type=3D"cite"><div>On 30 Apr =
2026, at 11:37=E2=80=AFAM, Adrian Klaver =
&lt;adrian.klaver@aklaver.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><span style=3D"caret-color: =
rgb(0, 0, 0); font-family: Helvetica-Light; font-size: 13px; font-style: =
normal; font-variant-caps: normal; font-weight: 400; letter-spacing: =
normal; text-align: start; text-indent: 0px; text-transform: none; =
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none; float: none; display: inline !important;">So in =
your first case the INSERT is never done and there is no lock for the =
INSERT in any case.</span><br style=3D"caret-color: rgb(0, 0, 0); =
font-family: Helvetica-Light; font-size: 13px; font-style: normal; =
font-variant-caps: normal; font-weight: 400; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;"></div></blockquote></div><br><div>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</div><div><br></div><div><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span>The FOR =
UPDATE lock mode is also acquired by any DELETE on a =
row=E2=80=A6</div><div><br></div><div>But I am not finding the info that =
talks about why the INSERT =E2=80=A6 ON CONFLICT DO NOTHING does block =
until the DELETE finishes. I guess in my mind the SELECT =E2=80=A6 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.</div><div><br></div><div>I suppose what I=E2=80=99d 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?</div><div><br></div><div>Cheers,</div><div>Matt</div></body></html>=

--Apple-Mail=_A528BCC8-165A-45C7-85E9-621E633890D6--