SSI: ON CONFLICT DO SELECT takes no predicate lock on the returned row

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: SSI: ON CONFLICT DO SELECT takes no predicate lock on the returned row
Date: 2026-08-19 11:33:01
Message-ID: CAN4CZFM1GkHJkpMeo4G5rxtacVsfeKCJYiik9E9AKX1E9VYQ1w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello!

While testing the new INSERT...ON CONFLICT DO SELECT feature I found a
possible serialization violation: the row returned back by DO SELECT
is not covered by any SIREAD lock, so a concurrent transaction can
modify it.

s1: INSERT INTO a VALUES (1,99) ON CONFLICT (key) DO SELECT RETURNING val;
s2: SELECT count(*) FROM b;
s2: UPDATE a SET val = 1 WHERE key = 1;
s1: INSERT INTO b VALUES (1,10);
s1: COMMIT;
s2: COMMIT;

And both transactions commit. If you replace the DO SELECT with a
plain SELECT, or SELECT FOR UPDATE s2 fails with a serialization
error, as expected.

The attached patch takes a predicate lock to fix the issue and adds an
isolation test covering the above example.

Attachment Content-Type Size
0001-Take-SIREAD-lock-on-rows-read-by-ON-CONFLICT-DO-SELE.patch application/octet-stream 6.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2026-08-19 11:44:26 Further cleanup related to statistics import support in postgres_fdw
Previous Message Alexander Korotkov 2026-08-19 11:31:53 Re: MERGE/SPLIT PARTITIONS issues/questions