Re: Atomicity?

I would like more information on this deficiency and what causes it so I
know when to anticipate it. This resulted in a rather nasty bug which
took me ages to track down. Is anyone able+willing to explain a little
here or should I ask in -hackers ?
Regards,
- Naz.

Michael Glaesemann wrote:
>
> On Aug 29, 2006, at 4:46 , Peter Eisentraut wrote:
>
>> Naz Gassiep wrote:
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>>> = 11;
>>> ERROR:  duplicate key violates unique constraint "replies_rgt_postid"
>>
>> This is a well-known deficiency in PostgreSQL.  You will have to work
>> around it somehow (by changing the query, the schema, or the index).
>
> One such workaround is:
>
> BEGIN;
>
> UPDATE replies
> SET rgt = -1 * (rgt + 2)
> WHERE postid = 18
>     AND rgt >= 11;
>
> UPDATE replies
> SET rgt = -1 * rgt
> WHERE rgt < 0;
>
> COMMIT;
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>