Re: BUG #1231: Probelm with transactions in stored code.

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