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
