Content-Type: text/plain;
	charset=utf-8
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
From: Matt Magoffin <postgresql.org@msqr.us>
In-Reply-To: <83819e975523be4d320141ff1363dfb40c82289b.camel@cybertec.at>
Date: Tue, 5 May 2026 07:53:39 +1200
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <0B23DB17-EB6B-4B56-9A61-D7A35A039DA2@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>
 <83819e975523be4d320141ff1363dfb40c82289b.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/0B23DB17-EB6B-4B56-9A61-D7A35A039DA2%40msqr.us>
Precedence: bulk


> On 1 May 2026, at 7:35=E2=80=AFAM, Laurenz Albe =
<laurenz.albe@cybertec.at> wrote:
>=20
> 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.

Yes, I had noticed that as well. In my case my goal is to both block and =
=E2=80=9Cdo nothing=E2=80=9D if after blocking a matching row is found. =
If this behaviour isn=E2=80=99t expected, I thought I could change to

ON CONFLICT DO UPDATE SET id =3D EXCLUDED.id

to essentially =E2=80=9Cdo nothing=E2=80=9D but I thought I would incur =
an actual update and I wanted to avoid the churn I presumed that would =
include.

Kind regards,
Matt=