public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: Order of update
Date: Sun, 20 Apr 2025 08:28:22 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On 4/20/25 02:10, Peter J. Holzer wrote:
> I've just read Laurenz' blog post about the differences between Oracle
> and PostgreSQL[1].
> 
> One of the differences is that something like
> 
>      UPDATE tab SET id = id + 1;
> 
> tends to fail on PostgreSQL because the the primary key constraint is
> checked for every row, so it will stumble over the temporary conflicts.
> 
> The solution is to define the constraint as deferrable.
> 
> But that got me to thinking about different ways ...
> 
> There won't be a conflict if the ids are updated in descending order.
> Is there a way to force PostgreSQL to update the rows in a specific
> order?
> 
> I came up with
> 
>      with a as (select id from t where id > 50 order by id desc)
>      update t set id = a.id+1 from a where t.id = a.id;
> 
> which works in my simple test case, but it doesn't look like it's
> guaranteed to work. The implicit join in «update t ... from a» could
> produce rows in any order, especially for large tables.

My read of this is that for the duration of the query a temporary table 
a is create that is ordered on `id desc` and that '... from a where t.id 
= a.id' will apply that order to the selection of t.id.


As example:

create table id_update(id integer primary key);

insert into id_update select a from generate_series(1, 100000) as t(a);
INSERT 0 100000

-- id(s) are temporarily in order.

update id_update set id = id where id between 50000 and 60000;
UPDATE 10001

-- The above move the 10001 values to 'end' of id_update

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

-- The UPDATE works even though the t.id(s) in id_update are not ordered 
-- by id


> 
> So, is there a better way?
> 
>          hjp
> 
> 
> [1] https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresq...
> 

-- 
Adrian Klaver
[email protected]

view thread (7+ messages)

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