On Sun, Dec 20, 2009 at 2:12 AM, Andrus <kobruleht2@hot.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.