Feedback-ID: i76984098:Fastmail
Message-ID: <e9bb687d-b7aa-46e2-ad0b-39ada582a545@aklaver.com>
Date: Mon, 21 Apr 2025 08:43:11 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Order of update
To: pgsql-general@lists.postgresql.org
References: <20250420091033.n437fdrkihtjrncd@hjp.at>
 <c71a961f-506d-43ad-92c6-f23831e408c5@aklaver.com>
 <20250421084717.4edbhu6a7duiqw42@hjp.at>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20250421084717.4edbhu6a7duiqw42@hjp.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/e9bb687d-b7aa-46e2-ad0b-39ada582a545%40aklaver.com>
Precedence: bulk

On 4/21/25 01:47, Peter J. Holzer wrote:

> 
> If the hash was the other way around it wouldn't work.
> 
> So let's try if we can get the optimizer to flip the plan by changing
> the number of updated rows.
> 
> [a few minutes later]
> 
> #v+
> hjp=> explain
> with a as (select id from id_update where id > 90000 order by id desc)
> update id_update as t set id = a.id + 1 from a  where a.id = t.id;
> ╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
> ║                                                         QUERY PLAN                                                         ║
> ╟────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╢
> ║ Update on id_update t  (cost=732.53..2675.61 rows=0 width=0)                                                               ║
> ║   ->  Hash Join  (cost=732.53..2675.61 rows=10006 width=38)                                                                ║
> ║         Hash Cond: (t.id = a.id)                                                                                           ║
> ║         ->  Seq Scan on id_update t  (cost=0.00..1443.00 rows=100000 width=10)                                             ║
> ║         ->  Hash  (cost=607.46..607.46 rows=10006 width=32)                                                                ║
> ║               ->  Subquery Scan on a  (cost=0.29..607.46 rows=10006 width=32)                                              ║
> ║                     ->  Index Only Scan Backward using id_update_pkey on id_update  (cost=0.29..507.40 rows=10006 width=4) ║
> ║                           Index Cond: (id > 90000)                                                                         ║
> ╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
> #v-
> 
> Looks like we got it.
> 
> And indeed:
> 
> #v+
> hjp=> with a as (select id from id_update where id > 90000 order by id desc)
>        update id_update as t set id = a.id + 1 from a  where a.id = t.id;
> ERROR:  duplicate key value violates unique constraint "id_update_pkey"
> DETAIL:  Key (id)=(90002) already exists.
> #v-
> 
> So, obviously that isn't guaranteed to work.

I read from here:

https://www.postgresql.org/docs/current/sql-update.html

"Use of an ORDER BY clause allows the command to prioritize which rows 
will be updated; it can also prevent deadlock with other update 
operations if they use the same ordering."

I went back to those docs and realized I had missed the FOR UPDATE in 
the example.

explain with a as (select id from id_update where id > 90000 order by id 
desc for update) update id_update as t set id = a.id + 1 from a  where 
a.id = t.id;
                                                  QUERY PLAN 

-------------------------------------------------------------------------------------------------------------
  Update on id_update t  (cost=3609.71..3856.94 rows=0 width=0)
    CTE a
      ->  LockRows  (cost=0.29..872.71 rows=9840 width=10)
            ->  Index Scan Backward using id_update_pkey on id_update 
(cost=0.29..774.31 rows=9840 width=10)
                  Index Cond: (id > 90000)
    ->  Hash Join  (cost=2737.00..2984.23 rows=9840 width=38)
          Hash Cond: (a.id = t.id)
          ->  CTE Scan on a  (cost=0.00..196.80 rows=9840 width=32)
          ->  Hash  (cost=1487.00..1487.00 rows=100000 width=10)
                ->  Seq Scan on id_update t  (cost=0.00..1487.00 
rows=100000 width=10)
(10 rows)

and then:

with a as (select id from id_update where id > 90000 order by id desc 
for update) update id_update as t set id = a.id + 1 from a  where a.id = 
t.id;
UPDATE 10000

Though at this point I would agree with you on the no guarantee point.


> 
>          hjp
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com