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

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