Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wIWjq-008Ayy-36 for pgsql-general@arkaria.postgresql.org; Thu, 30 Apr 2026 19:07:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wIWjo-008WwL-2a for pgsql-general@arkaria.postgresql.org; Thu, 30 Apr 2026 19:07:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wIWjo-008WwD-0A for pgsql-general@lists.postgresql.org; Thu, 30 Apr 2026 19:07:52 +0000 Received: from pmg-2.outbound.snap.net.nz ([202.37.100.107]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wIWjl-00000003Yuk-0lQY for pgsql-general@lists.postgresql.org; Thu, 30 Apr 2026 19:07:51 +0000 Received: from pmg-2.snap.net.nz (localhost.localdomain [127.0.0.1]) by pmg-2.snap.net.nz (Proxmox) with ESMTP id 0E82B3AFFDE; Fri, 1 May 2026 07:07:43 +1200 (NZST) Received: from smtp.snap.net.nz (smtp.snap.net.nz [202.37.100.140]) by pmg-2.snap.net.nz (Proxmox) with ESMTP id E712C15688; Fri, 1 May 2026 07:07:41 +1200 (NZST) Received: from x24.msqr.us (msqr.us [123.255.47.99]) by rupert.snap.net.nz (Postfix) with ESMTPS id D78B6E5; Fri, 1 May 2026 07:07:41 +1200 (NZST) Received: from smtpclient.apple (unifi.localdomain [192.168.1.1]) (authenticated bits=0) by x24.msqr.us (8.18.2/8.16.1) with ESMTPSA id 63UJ7eEK078660 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NO); Fri, 1 May 2026 07:07:40 +1200 (NZST) (envelope-from postgresql.org@msqr.us) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=msqr.us; s=20121026; t=1777576061; bh=ptoQCQR1l2dpsFTCToWnc6BuSXCbn8rCn7w1ZwKARHM=; h=From:Subject:Date:In-Reply-To:Cc:To:References; b=WORO8YbEvngOx0JbgaE2U85ru29yZbbzfRjQLLd9NkGJ+Sak001M5lfO71Rz3NUtv HRhUkbA49jpM0l9hSIr/4y7OqtMABNZ17hZ3LIoQG6f4VdWrgv8/Hd7gQ8NI2pyoKf Pz/G4aln8tnbqHPBgFiEG1cWGeKyE9jzfNKky8JU= X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 1.5.2 at msqr.us From: Matt Magoffin Message-Id: <93ADF4E5-CF49-4347-8A79-33655A8E0299@msqr.us> Content-Type: multipart/alternative; boundary="Apple-Mail=_87E7D9A2-BA99-45B7-A6F3-4F33E38EDF38" 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: Fri, 1 May 2026 07:07:30 +1200 In-Reply-To: Cc: Adrian Klaver , pgsql-general@lists.postgresql.org To: Laurenz Albe References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us> <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us> X-Mailer: Apple Mail (2.3826.700.81.1.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_87E7D9A2-BA99-45B7-A6F3-4F33E38EDF38 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 30 Apr 2026, at 6:42=E2=80=AFPM, Laurenz Albe = wrote: >=20 > So I'd say that the documentation is not quite accurate. Really, 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 = transaction to complete before > it knows how to proceed. >=20 > I cannot say if that is intentional; as I said initially, I am = surprised too. Thank you for your additional insights, Laurenz. Kind regards, Matt= --Apple-Mail=_87E7D9A2-BA99-45B7-A6F3-4F33E38EDF38 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On 30 Apr = 2026, at 6:42=E2=80=AFPM, 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.

Kind = regards,
Matt
= --Apple-Mail=_87E7D9A2-BA99-45B7-A6F3-4F33E38EDF38--