Paramveer.Singh@trilogy.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