From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | Christophe Pettus <xof(at)thebuild(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to use read uncommitted transaction level and set update order |
Date: | 2009-12-20 09:24:45 |
Message-ID: | dcc563d10912200124w16279107p43ad5f10f3f7c48f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Dec 20, 2009 at 2:12 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
>> You cannot access new values of a particular row within a single UPDATE
>> statement, but you do see new values done in the same transaction.
>> This is explain in some detail in the documentation:
>>
>>
>> http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html#XACT-READ-COMMITTED
>
> I tried
>
> drop table if exists tt ;
> create temp table tt ( a int, b int );
> insert into tt values ( 1,2);
> insert into tt values ( 3,4);
> update tt set a=a*10, b=(select sum(a) from tt);
> select * from tt
>
> b has value 4 for every row.
>
> So we *dont* see new values done in the same transaction.
> How to fix ?
This isn't broken behaviour.
First the inserts run and we have
1,2
3,4
When the update fires, the right hand side of the key/value pairs are
evaluated simultaneously based on the data in the table AT THE TIME
The query starts. b=sum(a) means b=sum(3,1) which means you're
setting b=4... This was explained in a previous post by Tom I
believe. Unless I'm missing what you're saying.
From | Date | Subject | |
---|---|---|---|
Next Message | JGuillaume (ioguix) de Rorthais | 2009-12-20 10:09:54 | Re: Extracting SQL from logs in a usable format |
Previous Message | Andrus | 2009-12-20 09:12:39 | Re: How to use read uncommitted transaction level and set update order |