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

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