Re: order of row processing affects updates

Paramveer(dot)Singh(at)trilogy(dot)com writes:

> Hi all!
> consider the following table
>
> table a (id int primary key)
> and a particular instance of it:
> id
> ------------------------
> 5
> 6
>
> now update a set id = id +1;
> fails if the executor processes row with 5 first.

Well the correct way to make this always work would be to make the unique
constraint deferrable and set constraints to be deferred. However Postgres
doesn't support deferring unique constraints.

I don't think there's any practical way to guarantee the ordering of the
update. You could cluster the table on the unique index which would guarantee
it will fail. But clustering is a slow operation and it would have to be done
before every update like this.

To make it work I think the usual work-around is to update the ids to be in a
different range, and then update them to the final values. Something like:

BEGIN;
UPDATE a SET id = -id;
UPDATE a SET id = -id + 1;
COMMIT;

--
greg