Re: Atomicity?

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

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