The following bug has been logged online:
Bug reference: 1231
Logged by: Piotr Figiel
Email address: p.figiel@aplok.pl
PostgreSQL version: 7.4.3
Operating system: Linux Suse
Description: Probelm with transactions in stored code.
Details:
Hello
I have a problem with transactions in stored code in database.
This is testcase:
create table test_trans
( id numeric(4,0),
next_number numeric(4,0)
);
insert into test_trans values (1,1);
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.
Reagards
Piotr Figiel
