Naz Gassiep wrote:
No, the subsequent UPDATEs were just there to show you they worked... I
was only interested in the failed update, and why it failed. The DB was
consistent before the query, and it would have been after the query, so
I did not understand why the query failed unless the query made teh DB
inconsistent at some point DURING its execution. This seems odd to me,
as queries should not trigger errors like that if the DB is only out of
consistency DURING its execution, as long as it is consistent before and
after.
Yeah I misunderstood your question. See PeterE's response.
Sincerely,
Joshua D. Drake
Regards,
- Naz.
Joshua D. Drake wrote:
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/
