Обсуждение: Lost update in an ordered batch, but only with index scan

Hi,

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

I've been following the recommendation of ordering batch updates to
avoid deadlocks. I just added a new case, and it consistently loses
updates in my tests.

Here's a simplified version.

--setup
create table t(id int primary key, balance int default 0);
insert into t(id) select i from generate_series(1, 10000) i;
analyze t;

--session 1
begin;
with l as (select id from t where id in (1, 2) order by id for update)
update t set balance = balance + 1 from l where t.id = l.id;

UPDATE 2

--session 2
with l as (select id from t where id in (1, 2) order by id for update)
update t set balance = balance + 1 from l where t.id = l.id;

UPDATE 1 (after committing session 1)

If I only have a few rows in the table, it does a seq scan, and then
both rows update in session 2.

Apologies if I'm just doing something wrong and this isn't a bug.

Thanks, Bernice

On Mon, Dec 29, 2025 at 8:07 AM Bernice Southey
<bernice.southey@gmail.com> wrote:
> I've been following the recommendation of ordering batch updates to
> avoid deadlocks. I just added a new case, and it consistently loses
> updates in my tests.

Thanks for the report.  I think this is a bug.  git-bisect shows that:

cbc127917e04a978a788b8bc9d35a70244396d5b is the first bad commit
commit cbc127917e04a978a788b8bc9d35a70244396d5b
Author: Amit Langote <amitlan@postgresql.org>
Date:   Fri Feb 7 17:15:09 2025 +0900

    Track unpruned relids to avoid processing pruned relations

I didn't look further, but it seems like the same bug as reported in
[1].

[1] https://postgr.es/m/19355-57d7d52ea4980dc6@postgresql.org

- Richard