Skip site navigation (1) Skip section navigation (2)

BUG #1231: Probelm with transactions in stored code.

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1231: Probelm with transactions in stored code.
Date: 2004-08-25 18:43:17
Message-ID: 20040825184317.0FECE5A106F@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1231
Logged by:          Piotr Figiel

Email address:      p(dot)figiel(at)aplok(dot)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


Responses

pgsql-bugs by date

Next:From: Maksim LikharevDate: 2004-08-25 20:58:40
Subject: Win32 Native port more then 64 connection
Previous:From: Tom LaneDate: 2004-08-25 18:33:15
Subject: Re: vm/swap used until exhausted

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group