Thread: Order of update

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.

So, is there a better way?

        hjp


[1] https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresql/

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Very interesting. But is the sort overhead worth it? Why not make the constraint deferrable before the update and
switchback afterwards?
 

On 2025-04-20 11:34:56 +0200, Thiemo Kellner wrote:
> Very interesting. But is the sort overhead worth it? Why not make the
> constraint deferrable before the update and switch back afterwards?

Mostly idle curiosity whether that's possible at all.

But there might be other reasons why you want to do updates in a
predictable order. For example to prevent deadlocks.

        hjp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Hm, deadlocks preventing order by. Never had that problem. Then again, I mostly have Oracle experience and no need for
complicatedupdates. If I had, I'd rather think of chunking updates and orchestrate those before ordering within
updates.

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-postgresql/
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com