public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Matt Magoffin <postgresql.org@msqr.us>
To: pgsql-general@lists.postgresql.org
Subject: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Thu, 30 Apr 2026 11:07:14 +1200
Message-ID: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us> (raw)

Hello, I was hoping to confirm some transaction behaviour I am seeing (in Postgres 17) in read-committed isolation mode that caught me off guard is, in fact, expected. First some setup:

CREATE TABLE txtest (id INTEGER NOT NULL PRIMARY KEY);
INSERT INTO txtest (id) VALUES (1);

Then in one session, I run:

BEGIN; SELECT * FROM txtest WHERE id = 1 FOR UPDATE;

Then, in a different session, I run:

INSERT INTO txtest
SELECT id
FROM (VALUES
	(1),
	(2)
) AS t(id)
ON CONFLICT
DO NOTHING;

This completes immediately, with 

INSERT 0 1

and indeed there are 2 rows now in that session:

SELECT * FROM txtest;
 id 
----
  1
  2

This is what caught be off guard, as I had been thinking the INSERT would block until the first session’s transaction finished. Now, back in session #1, I run:

DELETE FROM txtest WHERE ID = 1; COMMIT;

Now in both sessions there is 1 row, with “2”, where I had been hoping to end up with both “1” and “2” after the INSERT waited for the SELECT … FOR UPDATE to complete first.

If I change session #1’s query from SELECT … FOR UPDATE to an immediate DELETE, I get what I expected, i.e.

BEGIN; DELETE FROM txtest WHERE id = 1;

Then in session #1 the same INSERT … ON CONFLICT DO NOTHING statement blocks until session #1 commits, and it results in 

INSERT 0 2

The difference in transaction behaviour between SELECT … FOR UPDATE and DELETE I did not understand from the documentation, so would appreciate any confirmation/clarification/insight on what I’m seeing so I can better understand.

Thank you,
Matt Magoffin

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, pgsql-general@lists.postgresql.org
  Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
  In-Reply-To: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@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