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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
