Thread: Help with SET TRANSACTION in a function

Help with SET TRANSACTION in a function

From

otis_usenet@yahoo.com (OtisUsenet)

Date:

04 March 2002, 17:57:15

Hello,

I was wondering if anyone here can help.  I could not get any help on
pgsql-sql  nor pgsql-general mailing lists.

I'm having trouble getting functions with SET TRANSACTION ISOLATION
... to parse.

This is the error I'm getting (example function below the error):

=> select simple_fun();
NOTICE:  plpgsql: ERROR during compile of simple_fun near line 3
ERROR:parse error at or near ";"

This is the simple_fun function that is causing the above error:

CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN;
  RETURN 1;
  END;
END;
' LANGUAGE 'plpgsql';

This is as simple as it gets.
I think my syntax is correct (I checked Practical PostgreSQL
book as well as a number of 7.2 PDF documents, etc.).

Am I missing a secret ingredient here?

Thank you,
Otis

Re: Help with SET TRANSACTION in a function

From

Stephan Szabo

Date:

04 March 2002, 19:20:42

On 28 Feb 2002, OtisUsenet wrote:

> Hello,
>
> I was wondering if anyone here can help.  I could not get any help on
> pgsql-sql  nor pgsql-general mailing lists.
>
> I'm having trouble getting functions with SET TRANSACTION ISOLATION
> ... to parse.

Without nested transactions, you're not going to have any luck I think.
First of all the extra begin/end in the function are not allowed and
IIRC, set transaction isolation level must be the first statement of the
transaction which it won't be since the enclosing transaction will have
started the select simple_fun().

Re: Help with SET TRANSACTION in a function

From

Oliver Elphick

Date:

04 March 2002, 19:54:56

On Thu, 2002-02-28 at 16:57, OtisUsenet wrote:
> This is the simple_fun function that is causing the above error:
>
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> BEGIN
>   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>   BEGIN;
>   RETURN 1;
>   END;
> END;
> ' LANGUAGE 'plpgsql';
>
> This is as simple as it gets.
> I think my syntax is correct (I checked Practical PostgreSQL
> book as well as a number of 7.2 PDF documents, etc.).
>
> Am I missing a secret ingredient here?

A function already happens inside a transaction, whether implicit or
explicit.  You cannot start another transaction inside it; PostgreSQL
does not support nested transactions.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Give, and it will be given to you. A good measure,
      pressed down, taken together and running over,
      will be poured into your lap. For with the same
      measure that you use, it will be measured to
      you."         Luke 6:38

Re: Help with SET TRANSACTION in a function

From

"otisg"

Date:

05 March 2002, 01:07:11

Hello,
> From: Stephan Szabo
> On 28 Feb 2002, OtisUsenet wrote:
>
> I was wondering if anyone here can help. I could not get any help on
> pgsql-sql nor pgsql-general mailing lists.
> >
> I'm having trouble getting functions with SET TRANSACTION ISOLATION
> ... to parse.
>
> Without nested transactions, you're not going to have any luck I think.
> First of all the extra begin/end in the function are not allowed and
> IIRC, set transaction isolation level must be the first statement of the
> transaction which it won't be since the enclosing transaction will have
> started the select simple_fun().

Hello,

If I understand it correctly the function should then look like this:

CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
RETURN 1;
END;
' LANGUAGE 'plpgsql';

I have loaded that into my database, but I still get an error:

select simple_fun();
NOTICE: plpgsql: ERROR during compile of simple_fun near line 1
ERROR: parse error at or near "SET"

Am I still doing something wrong?

Thanks,
Otis
_______________________________________________________________
Get your own FREE email account at iVillage.com!
http://webmail.ivillage.com/

Re: Help with SET TRANSACTION in a function

From

Darren Ferguson

Date:

05 March 2002, 02:02:42

Should you not have DECLARE before you do the SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE

From what i have seen this is what you would need although i am not
absolutely positive about this one

Darren Ferguson

On Mon, 4 Mar 2002, otisg wrote:

> Hello,
> > From: Stephan Szabo
> > On 28 Feb 2002, OtisUsenet wrote:
> >
> > I was wondering if anyone here can help. I could not get any help on
> > pgsql-sql nor pgsql-general mailing lists.
> > >
> > I'm having trouble getting functions with SET TRANSACTION ISOLATION
> > ... to parse.
> >
> > Without nested transactions, you're not going to have any luck I
> think.
> > First of all the extra begin/end in the function are not allowed and
> > IIRC, set transaction isolation level must be the first statement of
> the
> > transaction which it won't be since the enclosing transaction will
> have
> > started the select simple_fun().
>
> Hello,
>
> If I understand it correctly the function should then look like this:
>
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';
>
> I have loaded that into my database, but I still get an error:
>
> select simple_fun();
> NOTICE: plpgsql: ERROR during compile of simple_fun near line 1
> ERROR: parse error at or near "SET"
>
> Am I still doing something wrong?
>
> Thanks,
> Otis
> _______________________________________________________________
> Get your own FREE email account at iVillage.com!
> http://webmail.ivillage.com/
>

Re: Help with SET TRANSACTION in a function

From

Stephan Szabo

Date:

05 March 2002, 02:05:47

On Mon, 4 Mar 2002, otisg wrote:

> Hello,
> > From: Stephan Szabo
> > On 28 Feb 2002, OtisUsenet wrote:
> >
> > I was wondering if anyone here can help. I could not get any help on
> > pgsql-sql nor pgsql-general mailing lists.
> > >
> > I'm having trouble getting functions with SET TRANSACTION ISOLATION
> > ... to parse.
> >
> > Without nested transactions, you're not going to have any luck I
> think.
> > First of all the extra begin/end in the function are not allowed and
> > IIRC, set transaction isolation level must be the first statement of
> the
> > transaction which it won't be since the enclosing transaction will
> have
> > started the select simple_fun().
>
> Hello,
>
> If I understand it correctly the function should then look like this:
>
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';

Only declarations go outside the begin. As far as I can see, you
really just can't put a set transaction isolation level in a function
and expect it to work. If PostgreSQL had nested transactions you could
presumably do it then, but it doesn't.

Re: Help with SET TRANSACTION in a function

From

"otisg"

Date:

05 March 2002, 02:50:45

Hello,

> From: Stephan Szabo
> On Mon, 4 Mar 2002, otisg wrote:
> > From: Stephan Szabo
> > On 28 Feb 2002, OtisUsenet wrote:
> >
> > I was wondering if anyone here can help. I could not get any help on
> > pgsql-sql nor pgsql-general mailing lists.
> > >
> > I'm having trouble getting functions with SET TRANSACTION ISOLATION
> > ... to parse.
> >
> > Without nested transactions, you're not going to have any luck I
> think.
> > First of all the extra begin/end in the function are not allowed and
> > IIRC, set transaction isolation level must be the first statement of
> the
> > transaction which it won't be since the enclosing transaction will
> have
> > started the select simple_fun().
> >
> Hello,
> >
> If I understand it correctly the function should then look like this:
> >
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';
>
> Only declarations go outside the begin. As far as I can see, you
> really just can't put a set transaction isolation level in a function
> and expect it to work. If PostgreSQL had nested transactions you could
> presumably do it then, but it doesn't.

So how does one use SET TRANSACTION...?
I have not been able to find any examples of that in the docs, other than the reference document.
Could you please provide a simple example of how SET TRANSACTION... is used?

Thank you,
Otis
_______________________________________________________________
Get your own FREE email account at iVillage.com!
http://webmail.ivillage.com/

Re: Help with SET TRANSACTION in a function

From

Stephan Szabo

Date:

05 March 2002, 04:17:11

On Mon, 4 Mar 2002, otisg wrote:

> > Only declarations go outside the begin. As far as I can see, you
> > really just can't put a set transaction isolation level in a function
> > and expect it to work. If PostgreSQL had nested transactions you could
> > presumably do it then, but it doesn't.
>
> So how does one use SET TRANSACTION...?
> I have not been able to find any examples of that in the docs, other
> than the reference document.
> Could you please provide a simple example of how SET TRANSACTION... is
> used?

You use it directly in the sequence of sql commands (for example in psql)
begin;
set transaction isolation level serializable;
select funcfoo();
select * from sometable;
update sometable set somevalue=3 where somevalue=4;
end;

AFAICS you just can't use it inside a function since it needs to be before
the first query the transaction does (the error message it gives otherwise
basically says so anyway) and to get to a function means you're already
processing a query.