Naz Gassiep wrote:
I am getting an error that I think I understand, but that I didn't think
should happen.
Below is the output from psql that I am getting to trigger this error.
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. Or do I have my understanding
all wrong?
In any case, how do I get around this problem?
If you do not specify the beginning of a transaction, all statements are
run within their own transaction.. e;g:
Your example actually means:
begin;
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >=
11;
commit;
begin;
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 14;
commit;
What you want is:
begin;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 13;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 12;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 11;
UPDATE 1
conwatch=#
commit;
Joshua D. Drake
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
