Re: How to use read uncommitted transaction level and set update order

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.