Re: Atomicity?

["Joshua D. Drake" <jd(at)commandprompt(dot)com>]

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/