Stephan Szabo wrote:
On Wed, 25 Aug 2004, PostgreSQL Bugs List wrote:
CREATE OR REPLACE FUNCTION test_tr() RETURNS numeric AS' DECLARE a numeric; b numeric; BEGIN select next_number into b from test_trans where id=1; update test_trans set next_number=next_number+1 where id=1; select next_number into a from test_trans where id=1;
RETURN a ; END; ' LANGUAGE 'plpgsql' VOLATILE;
What I do then. I've run two sessions. In first I've run test_trans(), then in second I've run test_trans() too. Second sessions waiting for first commit or rollback. Very good. Then I've commited first session. What I see then: First session returned value 2 - very good, but second session returned value 1 - poor, oooo poor. Why , why, why? Second session should returned value 3. What happends. In version 8.0 Beta is the same situation. Additionl info: I've must user read commited transacion isolation. Please answer for my problem. My application based on this database but this problem show everyone that PostgreSQL is not a transactional database.
Actually, it shows that functions have odd behavior when locking is involved (your statement would potentially be true if you could replicate this without the functions). IIRC, there are issues currently with which rows you see in such functions unless you end up using FOR UPDATE on the selects or something of that sort.
If the first select is a "FOR UPDATE" nothing change. For sure the last select in that function doesn't see the same row if you perform that same select after the function execution, and for sure doesn't see the same row that the update statement touch.
Regards Gaetano Mendola
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
