The following bug has been logged on the website:
Bug reference: 19116
Logged by: Bernice Southey
Email address: bernice.southey@gmail.com
PostgreSQL version: 18.1
Operating system: Linux Mint 22.2
Description:
Here's a contrived exampled to reproduce. (The advisory locks are to force
concurrency.)
--session 1
CREATE TABLE t(p BOOL, q BOOL);
INSERT INTO t DEFAULT VALUES;
SELECT pg_advisory_lock(1);
--session 2
SELECT pg_advisory_lock_shared(1);
SELECT pg_advisory_unlock_shared(1);
WITH lock_t AS (SELECT ctid FROM t FOR UPDATE)
UPDATE t SET p = TRUE FROM lock_t l WHERE t.ctid = l.ctid
RETURNING p, q, t.ctid;
--session 3
SELECT pg_advisory_lock_shared(1);
SELECT pg_advisory_unlock_shared(1);
WITH lock_t AS (SELECT ctid FROM t FOR UPDATE)
UPDATE t SET q = TRUE FROM lock_t l WHERE t.ctid = l.ctid
RETURNING p, q, t.ctid;
--session 1
SELECT pg_advisory_unlock(1);
Only one of the updates succeeds. If using a standard column, instead of
ctid, then both updates succeed.
I was using a similar approach to the one advised in the UPDATE doc final
example[1]. This guidance was added in PostgreSQL 17 (see [2] for the
discussion). This wouldn't matter as used in the example, as subsequent
updates will find the lost updates. But it does matter, if this approach is
used to update a sync record (e.g. a balance).
I first thought this was the same issue as here [3] and wrote a post asking
for confirmation in the general mailing list. But I've now established that
patch was released in 17.7. I'm logging a bug because I think the doc
guidance might be dangerous.
I can reproduce this in:
PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
PostgreSQL 17.6 (Debian 17.6-2.pgdg13+1) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 14.2.0-19) 14.2.0, 64-bit
1 [https://www.postgresql.org/docs/current/sql-update.html]
2
[https://www.postgresql.org/message-id/flat/CADkLM%3DcaNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw%40mail.gmail.com]
3
[https://www.postgresql.org/message-id/flat/4a6268ff-3340-453a-9bf5-c98d51a6f729%40app.fastmail.com]
