Subject: Order of update

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 20 Apr 2025 11:10:33 +0200

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.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Thiemo Kellner <[email-masked]>
Date: Sun, 20 Apr 2025 11:34:56 +0200 (GMT+02:00)

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

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 20 Apr 2025 12:59:06 +0200

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

---

From: Thiemo Kellner <[email-masked]>
Date: Sun, 20 Apr 2025 16:03:43 +0200 (GMT+02:00)

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

---

From: Ron Johnson <[email-masked]>
Date: Sun, 20 Apr 2025 10:28:34 -0400

> constraint deferrable before the update and switch back afterwards?

The role which runs the UPDATE might not have the priv to ALTER TABLE ...
ALTER CONSTRAINT.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Thiemo Kellner <[email-masked]>
Date: Sun, 20 Apr 2025 16:52:25 +0200 (GMT+02:00)

Might that be a feature of or a flaw in the application design? I opt for the latter. Any application that needs updates, be it only in emergency cases, should take that into account.

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 20 Apr 2025 08:28:22 -0700

> 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

-- 
Adrian Klaver
[email-masked]