Re: Atomicity?

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.
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
>>
>
>