Subject: Changing the transaction isolation level within the stored procedure?
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art
Date: Wed, 25 Jan 2006 15:12:28 +0100
Message-Id: <1138198348.8828.32.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Is it possible to change the transaction level within the procedure?

I'm using Postgres 8.1.2 default isolation level. But, I would like one
stored procedure to execute as in serializable isolation level. I have
created my stored procedure like this:

CREATE OR REPLACE FUNCTION set_message_status("varchar", int4)
  RETURNS void AS
$BODY$
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	UPDATE messages SET message_status = $2 WHERE message_id = $1 AND
message_status < $2;
	INSERT INTO _update_log VALUES (now()::timestamp, $1, $2);$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;


But I'm getting error:

octopussy2=# select * from
set_message_status('b4c15204-123f-4cba-ad09-d423630c999d', 90);
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL function "set_message_status" statement 1


I get the same error if I write my stored procedure in plpgsql language.

What I want to achive goes like this:

I have a client code (java/jdbc) that inserts some messages to my
database, and then process them.

Basicaly, it goes like this:

One thread (thread A) does this:

1. java got the message via http (whatever)
2. java does: begin;
3. java does: select * from create_message(...)
4. java does some checking
5. java does: select * from set_message_status(...)
6. java does some more checing
7. java does commit; (under rare circumstances java does rollback).


Another thread (thread B) does this:

1. java got the update_status_request via http (whatever)
2. java does: begin;
3. java does: select * from set_message_status(...)
4. java does: commit;

As I've said, I'm using 'read commited', the default isolation level.

Now, sometimes it happens that steps 4 or 6 take more time, and thread B
steps are executed before steps in thread A have finished. So I would
like the UPDATE in set_message_status to 'hold', until the transaction
that previoulsy called the set_message_status have commited or rolled
back.

Is there a way to do so withing the postgres, or I need to do 'SET
TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both
thread A and thread B?

	Mike
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."