Re: order of row processing affects updates

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(at)mit(dot)edu>
Sent by: pgsql-general-owner(at)postgresql(dot)org
19/09/2004 10:33 PM

To: pgsql-general(at)postgresql(dot)org
cc:
Subject: Re: [GENERAL] 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org