Re: order of row processing affects updates
| От | Paramveer.Singh@trilogy.com |
|---|---|
| Тема | Re: order of row processing affects updates |
| Дата | |
| Msg-id | OFE14F79D2.7370F78D-ONE5256F15.002541E0@trilogy.com обсуждение исходный текст |
| Ответ на | order of row processing affects updates (Paramveer.Singh@trilogy.com) |
| Ответы |
Re: order of row processing affects updates
|
| Список | pgsql-general |
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 <gsstark@mit.edu> Sent by: pgsql-general-owner@postgresql.org 19/09/2004 10:33 PM | To: pgsql-general@postgresql.org cc: Subject: Re: [GENERAL] order of row processing affects updates |
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
В списке pgsql-general по дате отправления: