Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to mark a transaction as SERIALIZABLE?

How to mark a transaction as SERIALIZABLE?

From
Joe Carr
Date:
In version 9.0, I've been reading http://www.postgresql.org/docs/9.0/interactive/sql-begin.html. I've been using the following  : 

CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS SETOF integer AS
$BODY$

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

which returns with the error : 
ERROR:  syntax error at or near "TRANSACTION"
LINE 1: TRANSACTION ISOLATION LEVEL SERIALIZABLE

if the BEGIN line is empty with no semi-colon, the function will work :
CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS integer AS
$BODY$

BEGIN 
--TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

but if I place a semi-colon after BEGIN (e.g. BEGIN;), I get the error :
ERROR:  syntax error at or near ";"
LINE 5: BEGIN; 

So any help you may be able to provide in the BEGIN syntax (or whatever I'm doing wrong) is greatly appreciated. Thanks!

   -Joe

Re: How to mark a transaction as SERIALIZABLE?

From
Thom Brown
Date:
On 2 November 2010 01:51, Joe Carr <joe.carr@gmail.com> wrote:
In version 9.0, I've been reading http://www.postgresql.org/docs/9.0/interactive/sql-begin.html. I've been using the following  : 

CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS SETOF integer AS
$BODY$

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

which returns with the error : 
ERROR:  syntax error at or near "TRANSACTION"
LINE 1: TRANSACTION ISOLATION LEVEL SERIALIZABLE

if the BEGIN line is empty with no semi-colon, the function will work :
CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS integer AS
$BODY$

BEGIN 
--TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

but if I place a semi-colon after BEGIN (e.g. BEGIN;), I get the error :
ERROR:  syntax error at or near ";"
LINE 5: BEGIN; 

So any help you may be able to provide in the BEGIN syntax (or whatever I'm doing wrong) is greatly appreciated. Thanks!

   -Joe


BEGIN in your function isn't the same as BEGIN in an SQL statement block.  It merely marks where the function begins, not the transaction.  Note that you also shouldn't use a semi-colon after the BEGIN in a function.  See this page for more info: http://www.postgresql.org/docs/9.0/interactive/plpgsql-structure.html

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: How to mark a transaction as SERIALIZABLE?

From
Joe Carr
Date:
Thanks for the link. I've done some more reading, and now it appears impossible to set the isolation level of a transaction from within a function.

CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS integer AS
$BODY$
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

Creates successfully, but when executed reports : "ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query". I believe this is happening because I'm exercising the function with :

select * from test('test');

so that select is executed prior to the SET TRANSACTION. If I remove the SET TRANSACTION command from the function, and then call it by :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
select * from test('test');

that works. So, is that the correct way to set the isolation level for a function? Thanks again for your help.


On Tue, Nov 2, 2010 at 5:13 AM, Thom Brown <thom@linux.com> wrote:
On 2 November 2010 01:51, Joe Carr <joe.carr@gmail.com> wrote:
In version 9.0, I've been reading http://www.postgresql.org/docs/9.0/interactive/sql-begin.html. I've been using the following  : 

CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS SETOF integer AS
$BODY$

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

which returns with the error : 
ERROR:  syntax error at or near "TRANSACTION"
LINE 1: TRANSACTION ISOLATION LEVEL SERIALIZABLE

if the BEGIN line is empty with no semi-colon, the function will work :
CREATE OR REPLACE FUNCTION test(character varying)
  RETURNS integer AS
$BODY$

BEGIN 
--TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

RETURN 1;
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE

but if I place a semi-colon after BEGIN (e.g. BEGIN;), I get the error :
ERROR:  syntax error at or near ";"
LINE 5: BEGIN; 

So any help you may be able to provide in the BEGIN syntax (or whatever I'm doing wrong) is greatly appreciated. Thanks!

   -Joe


BEGIN in your function isn't the same as BEGIN in an SQL statement block.  It merely marks where the function begins, not the transaction.  Note that you also shouldn't use a semi-colon after the BEGIN in a function.  See this page for more info: http://www.postgresql.org/docs/9.0/interactive/plpgsql-structure.html

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: How to mark a transaction as SERIALIZABLE?

From
Tom Lane
Date:
Joe Carr <joe.carr@gmail.com> writes:
> Thanks for the link. I've done some more reading, and now it appears
> impossible to set the isolation level of a transaction from within a
> function.

That's correct.  By the time the function gets control, you're already
inside the transaction; it's too late to change its isolation level.

            regards, tom lane

Re: How to mark a transaction as SERIALIZABLE?

From
Chris Browne
Date:
joe.carr@gmail.com (Joe Carr) writes:
> so that select is executed prior to the SET TRANSACTION. If I remove the SET
> TRANSACTION command from the function, and then call it by :
>
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
> select * from test('test');
>
> that works. So, is that the correct way to set the isolation level for a
> function? Thanks again for your help.

In effect, it's an illusion that you "set the isolation level for a
function."

Functions run in the pre-existing context of an existing transaction.
(Yes, "existing" is pretty redundant there :-).)

You already set the isolation level for the transaction - the function
just uses the isolation already defined.

If a function expects a particular isolation level, one could mandate
this by checking the isolation level in the function, and raising an
error if it doesn't meet up with expectations.

The Postgres TODO list has an item, "Implement stored procedures" where
the work suggested indicates:

   "This might involve the control of transaction state and the return
   of multiple result sets"

That control of transaction state is what you're trying to do; it's not
done, so that functionality doesn't exist at this time.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://www3.sympatico.ca/cbbrowne/postgresql.html
"If you give a man a fish, he will eat for a day. If you teach him how
to fish, he will sit in a boat and drink beer all day."