| 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 |
| 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 |