public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: [email protected]
Subject: Re: Order of update
Date: Mon, 21 Apr 2025 08:43:11 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
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
[email protected]
view thread (6+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Order of update
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox