Changing the transaction isolation level within the stored procedure?

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(dot)splivalo(at)mobart(dot)hr

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