Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

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