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?