hi all!
thanks for all the feedback on row processing order.
I agree with Greg when he says that the correct way to do this is to set constraints to be deferred.
I think trying to predict a correct row processing order would be really complicated and the problem may not scale given the complexity of queries. (just guessing here)
Also, I would not like to rely on work-arounds like mapping the ids to a different range because this may not be immediately applicable to all inputs/situations.
Can someone tell me why postgres does not support deferring unique constraints?
thanks
paraM
| Greg Stark <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 19/09/2004 10:33 PM
|
To: [EMAIL PROTECTED] cc: Subject: Re: [GENERAL] order of row processing affects updates |
[EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
