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@xxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
