From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bernice.southey@gmail.com
Subject: BUG #19116: Lost concurrent updates using ctid - as per doc guidance
Date: Mon, 17 Nov 2025 14:31:11 +0000
Message-ID: <19116-079f845bd20241db@postgresql.org> (raw)
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%40m...]
3
[https://www.postgresql.org/message-id/flat/4a6268ff-3340-453a-9bf5-c98d51a6f729%40app.fastmail.com]
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bernice.southey@gmail.com
Subject: Re: BUG #19116: Lost concurrent updates using ctid - as per doc guidance
In-Reply-To: <19116-079f845bd20241db@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox