Message-ID: <83819e975523be4d320141ff1363dfb40c82289b.camel@cybertec.at>
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT
 DO NOTHING
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matt Magoffin <postgresql.org@msqr.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general@lists.postgresql.org
Date: Thu, 30 Apr 2026 21:35:43 +0200
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/83819e975523be4d320141ff1363dfb40c82289b.camel%40cybertec.at>
Precedence: bulk

On Fri, 2026-05-01 at 07:07 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 6:42=E2=80=AFPM, Laurenz Albe <laurenz.albe@cybertec=
.at> wrote:
> >=20
> > So I'd say that the documentation is not quite accurate. =C2=A0Really, =
the DELETE does not place
> > a row lock on the row.
> >=20
> > 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 transact=
ion to complete before
> > it knows how to proceed.
> >=20
> > I cannot say if that is intentional; as I said initially, I am surprise=
d too.
>=20
> 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