Naz Gassiep wrote:
> If the violation of the constraint really is being caused
> WITHIN the query, doesn't that violate the principle of atomicity?
> I.e., operations and entities should be considered a single entire
> construct rather than a collection of smaller, discrete parts.
The principle of atomicity merely says that transactions are either
performed entirely or not at all. If the transaction is not performed,
then there is no violation of atomicity.
> 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).
--
Peter Eisentraut
http://developer.postgresql.org/~petere/