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

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.