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."
