Re: Atomicity?

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/