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

How to use column values set in update in subsequent set clauses and in
subqueries in subsequent row updates?

I tried

set transaction isolation level read uncommitted;
create temp table test1 ( a int, b int) on commit drop;
insert into test1 values(1,2);
update test1 set a=4, b=a ;
select * from test1

b value is 1 but must be 4.
How to use updated value ?


For update order I tried

set transaction isolation level read uncommitted;
create temp table test1 ( a int, b int, c int) on commit drop;
insert into test1 values(1,2,3);
update test1 set a=4, b=a order by c ;
select * from test1

but got syntax error at order by.

How to specify update order ?

Andrus.

2009/12/19 Andrus <kobruleht2@hot.ee>:
>
> set transaction isolation level read uncommitted;

the "isolation level" is for specifying what rows are visible no for columns.
besides, postgres doesn't implement "read uncommitted"

> update test1 set a=4, b=a ;
>
> b value is 1 but must be 4.

no. b value "must be" 1, you want it to be 4...
in an update the columns always hold the old value until the statement
is finished, the only way i can think for doing this is with a trigger

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Dec 19, 2009, at 11:24 AM, Andrus wrote:
> set transaction isolation level read uncommitted;
> create temp table test1 ( a int, b int) on commit drop;
> insert into test1 values(1,2);
> update test1 set a=4, b=a ;
> select * from test1
>
> b value is 1 but must be 4.
> How to use updated value ?

The problem here isn't the transaction isolation level.  The order of
evaluation in an UPDATE statement is (for practical purposes):
Evaluate all of the right-hand side expressions, and then assign them
all to the left-hand side fields.

It's not clear why you need to do it this way, though.  Presumably,
since you did some kind of computation that came up with the number
'4', you can assign that value instead of using the field a:

UPDATE test1 set a=4, b=4;

--
-- Christophe Pettus
    xof@thebuild.com

Christophe Pettus <xof@thebuild.com> writes:
> On Dec 19, 2009, at 11:24 AM, Andrus wrote:
>> update test1 set a=4, b=a ;

>> How to use updated value ?

> The problem here isn't the transaction isolation level.  The order of
> evaluation in an UPDATE statement is (for practical purposes):
> Evaluate all of the right-hand side expressions, and then assign them
> all to the left-hand side fields.

This is required by the SQL standard, and always has been --- I quote SQL92:

         6) The <value expression>s are effectively evaluated before updat-
            ing the object row. If a <value expression> contains a reference
            to a column of T, then the reference is to the value of that
            column in the object row before any value of the object row is
            updated.

I would be quite surprised if there are any SQL databases that do this
differently.

            regards, tom lane

> I would be quite surprised if there are any SQL databases that do this
> differently.

FoxPro's and probably dBase's do it differently.

CREATE CURSOR t ( a i, b i )
INSERT INTO t VALUES (1,2)
UPDATE t SET a=3, b=a
SELECT * FROM t

returns 3 for b

Andrus.

Christophe,

> It's not clear why you need to do it this way, though.  Presumably,  since
> you did some kind of computation that came up with the number  '4', you
> can assign that value instead of using the field a:
>
> UPDATE test1 set a=4, b=4;

There are two reasons:

1. In my case b expression needs values from previous rows updated in this
same command before:

b= (select sum(a) from test1 where
<select_test1_previously_updated_rows_condition> )

I understood from replies that

set transaction isolation level read uncommitted;

in PostgreSql is broken: it sets silently committed isolation level.

I understand that it is not possible to read previous rows without creating
hack using triggers.

2. In my planned UPDATE statement instead of 4 there is an expression
containing one big CASE WHEN expression with many WHEN .. THEN  clauses.
This command  takes several hundreds of lines.
Your solution requires repeating this expression two times and thus makes
sql difficult to read.

It seems that splitting update statement into separate UPDATE commands in
proper order, one for every  column and commiting transaction after every
update is the only solution.
Fortunately in my case it is allowed to split every column update to
separate transaction.

Andrus.

On Dec 19, 2009, at 3:34 PM, Andrus wrote:
> FoxPro's and probably dBase's do it differently.

Of course, FoxPro and related are not actually relational databases;
they're flat-file managers which use comamnds which somewhat resemble
the SQL syntax.

--
-- Christophe Pettus
    xof@thebuild.com

On Dec 19, 2009, at 4:06 PM, Andrus wrote:
> 1. In my case b expression needs values from previous rows updated
> in this same command before:
>
> b= (select sum(a) from test1 where
> <select_test1_previously_updated_rows_condition> )
>

I believe there is a misunderstanding as to what "read committed"
isolation level means.  Read committed means that a particular
transaction will not see uncommitted work in a *different
transaction*.  It *does* see uncommitted work done previously in the
same transaction.  So, if you do:

BEGIN;

UPDATE table1 SET a=1 WHERE b=2;
SELECT a FROM table1 WHERE b=2;

You will get back 1, even before a COMMIT.

> I understand that it is not possible to read previous rows without
> creating hack using triggers.

As noted above, that's not correct.  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

> 2. In my planned UPDATE statement instead of 4 there is an
> expression containing one big CASE WHEN expression with many WHEN ..
> THEN  clauses.
> This command  takes several hundreds of lines.
> Your solution requires repeating this expression two times and thus
> makes sql difficult to read.

If it is an invariant condition of your database schema that two
particular columns must always have the same value, a trigger is an
appropriate way of enforcing that.

> It seems that splitting update statement into separate UPDATE
> commands in proper order, one for every  column and commiting
> transaction after every update is the only solution.

Again, it does seem you are not quite understanding what read
committed isolation mode actually means; I'd encourage you to read the
documentation.

--
-- Christophe Pettus
    xof@thebuild.com

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

Andrus.

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.

You are confusing a few things, and you don't want to hear the
explanations because they are inconvenient.

Andrus wrote:
> 1. In my case b expression needs values from previous rows updated in this
> same command before:

You are confusing "to the left of" and "before".
If you want behaviour that deviates from the SQL standard, you will
usually meet fierce resistance from PostgreSQL.

> I understood from replies that
>
> set transaction isolation level read uncommitted;
>
> in PostgreSql is broken: it sets silently committed isolation level.

You should read this:
http://www.postgresql.org/docs/8.4/static/transaction-iso.html

I agree that the behaviour may be surprising, but "broken" is polemic.
This is not always a good comparison when standard behaviour is
concerned, but Oracle does it the same way.

> 2. In my planned UPDATE statement instead of 4 there is an expression
> containing one big CASE WHEN expression with many WHEN .. THEN  clauses.
> This command  takes several hundreds of lines.
> Your solution requires repeating this expression two times and thus makes
> sql difficult to read.

... plus the expression would be evaluated twice. But you cannot hold that against
the person who gave you the advice, because you hid that fact.

Why don't you let your imagination play a little:

1) You could use a subquery like
  UPDATE foo SET col = myex
  FROM (SELECT foo_id, <your 100 lines here> AS myex FROM whatever ...) AS bar
  WHERE foo.foo_id = bar.foo_id;
2) You could define a stable SQL function for your 100 line subquery which
  should be evaluated only once in the UPDAT query.

Yours,
Laurenz Albe

Hi, Andrus,

First, it does seem that you are expecting PostgreSQL to have the same
behavior as a flat-file manager such as FoxPro (indeed, it seems you'd
like PG to have the behavior of a *specific* flat-file manager).
Despite the superficial similarity in the command syntax, a modern
RDBMS is a very different animal from FoxPro, dBase, 4D and the like,
and needs to be approached on its own terms rather than expecting the
semantics of commands with the same keyword to be the same.  While
that may seem to be an irritating and pointless transition, modern
RDBMSes are so much more powerful than flat-file managers that you'll
find the transition well worth your time.

On Dec 20, 2009, at 1:12 AM, Andrus wrote:

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

You seem to have a specific model for execution in mind, and that
model is not the one PostgreSQL (or any other standards-compliant SQL
database) will use.  Within each UPDATE statement, the UPDATE is
operating on a snapshot of the database at the time the command begins
execution.  That's what the SQL standard requires, as Tom Lane noted
earlier.

If you want to iterate through each row, applying changes, using PL/
pgSQL with cursors is probably the best solution:

    http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html

If you can be a bit more detailed about what you are trying to
accomplish, we can help you more.
--
-- Christophe Pettus
    xof@thebuild.com

On Sat, Dec 19, 2009 at 7:16 PM, Christophe Pettus <xof@thebuild.com> wrote:
>
>> I understand that it is not possible to read previous rows without
>> creating hack using triggers.
>
> As noted above, that's not correct.  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.
>

what´s the problem with something as simple as:

create function keep_a_in_b_test1() returns trigger as $$
begin
   new.b = old.a;
   return new;
end;
$$ language plpgsql;

create trigger trg_keep_a_in_b_test1 before update
on test1 for each row execute procedure keep_a_in_b_test1();

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157