Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux