Thread: Savepoints in PL/pgSQL

I understand that due to a lack of nested transaction support, it is
not possible to use START TRANSACTION within a PL/PgSQL function. What
I, however, do not understand is why I can't use SAVEPOINT either. I'm
writing long test functions wherein, at the start of the function, I'd
like to define all test data followed by a "SAVEPOINT
fresh_test_data;". Will this become possible in the (near) future? I
mean, savepoints are of limited use to me if they imply that I can't
stick my tests in stored procedures.

On a side note: I've seen a comment on this list that the error message
of trying to use a SAVEPOINT in PL/pgSQL isn't too clear (compared with
the error message for using SAVEPOINT in an SQL function). I can second
this. However, I noticed that the message is much clearer when you
EXECUTE the SAVEPOINT command.

On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsmoke@gmail.com> wrote:
> I understand that due to a lack of nested transaction support, it is
> not possible to use START TRANSACTION within a PL/PgSQL function. What
> I, however, do not understand is why I can't use SAVEPOINT either. I'm
> writing long test functions wherein, at the start of the function, I'd
> like to define all test data followed by a "SAVEPOINT
> fresh_test_data;". Will this become possible in the (near) future? I
> mean, savepoints are of limited use to me if they imply that I can't
> stick my tests in stored procedures.
>

Use

BEGIN

  ...

EXCEPTION

  ...

END;

Blocks instead. The pl/pgsql exception handling is implemented on top
of PostgreSQL's SAVEPOINT infrastructure. We are lacking user defined
exception support, but you can raise generic errors with RAISE EXCEPTION.

See

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

for details.

Bernd

On Dec 19, 4:16 pm, maili...@oopsware.de (Bernd Helmle) wrote:
> On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsm...@gmail.com> wrote:
>
> > I understand that due to a lack of nested transaction support, it is
> > not possible to use START TRANSACTION within a PL/PgSQL function. What
> > I, however, do not understand is why I can't use SAVEPOINT either. I'm
> > writing long test functions wherein, at the start of the function, I'd
> > like to define all test data followed by a "SAVEPOINT
> > fresh_test_data;". Will this become possible in the (near) future? I
> > mean, savepoints are of limited use to me if they imply that I can't
> > stick my tests in stored procedures.Use
>
> BEGIN
>
>   ...
>
> EXCEPTION
>
>   ...
>
> END;
>
> Blocks instead. The pl/pgsql exception handling is implemented on top
> of PostgreSQL's SAVEPOINT infrastructure. We are lacking user defined
> exception support, but you can raise generic errors with RAISE EXCEPTION.

I can't solve my problem with a BEGIN EXCEPTION END block because of
what I do in these functions. Here's an example function.

CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
BEGIN
  -- Define some test data

  -- SAVEPOINT fresh_test_data; -- If only I could ...

  IF some_test_assertion_fails THEN
    RAISE EXCEPTION 'Some informative message';
  END IF;

  -- ROLLBACK TO SAVEPOINT fresh_test_data;
END;
$$ LANGUAGE plpgsql;

In these functions, I raise an exception whenever a test fails. Now, If
I want to create an implicit savepoint using BEGIN/END blocks around
individual tests, I don't see how I can still sanely preserve this
behavior without the most horrid of hacks. The following code is what I
think I would need to do to emulate savepoints without direct access to
them. :-(  (I hope that I'm missing something.)

CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
BEGIN
  -- Define some test data

  BEGIN
    -- This is a useful test ;-)
    IF TRUE THEN
      RAISE EXCEPTION 'Aaargh! The test failed!';
    END IF;

    RAISE EXCEPTION '__dummy_restore_state__';

  EXCEPTION WHEN raise_exception THEN
    IF SQLERRM != '__dummy_restore_state__' THEN
      RAISE EXCEPTION '%', SQLERRM;
    END IF;
  END;
END;
$$ LANGUAGE plpgsql;

On Dec 19, 5:00 pm, "BigSmoke" <bigsm...@gmail.com> wrote:
> On Dec 19, 4:16 pm, maili...@oopsware.de (Bernd Helmle) wrote:
>
>
>
> > On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsm...@gmail.com> wrote:
>
> > > I understand that due to a lack of nested transaction support, it is
> > > not possible to use START TRANSACTION within a PL/PgSQL function. What
> > > I, however, do not understand is why I can't use SAVEPOINT either. I'm
> > > writing long test functions wherein, at the start of the function, I'd
> > > like to define all test data followed by a "SAVEPOINT
> > > fresh_test_data;". Will this become possible in the (near) future? I
> > > mean, savepoints are of limited use to me if they imply that I can't
> > > stick my tests in stored procedures.Use
>
> > BEGIN
>
> >   ...
>
> > EXCEPTION
>
> >   ...
>
> > END;
>
> > Blocks instead. The pl/pgsql exception handling is implemented on top
> > of PostgreSQL's SAVEPOINT infrastructure. We are lacking user defined
> > exception support, but you can raise generic errors with RAISE EXCEPTION.I can't solve my problem with a BEGIN
EXCEPTIONEND block because of 
> what I do in these functions. Here's an example function.
>
> CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
> BEGIN
>   -- Define some test data
>
>   -- SAVEPOINT fresh_test_data; -- If only I could ...
>
>   IF some_test_assertion_fails THEN
>     RAISE EXCEPTION 'Some informative message';
>   END IF;
>
>   -- ROLLBACK TO SAVEPOINT fresh_test_data;
> END;
> $$ LANGUAGE plpgsql;
>
> In these functions, I raise an exception whenever a test fails. Now, If
> I want to create an implicit savepoint using BEGIN/END blocks around
> individual tests, I don't see how I can still sanely preserve this
> behavior without the most horrid of hacks. The following code is what I
> think I would need to do to emulate savepoints without direct access to
> them. :-(  (I hope that I'm missing something.)
>
> CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
> BEGIN
>   -- Define some test data
>
>   BEGIN
>     -- This is a useful test ;-)
>     IF TRUE THEN
>       RAISE EXCEPTION 'Aaargh! The test failed!';
>     END IF;
>
>     RAISE EXCEPTION '__dummy_restore_state__';
>
>   EXCEPTION WHEN raise_exception THEN
>     IF SQLERRM != '__dummy_restore_state__' THEN
>       RAISE EXCEPTION '%', SQLERRM;
>     END IF;
>   END;
> END;
> $$ LANGUAGE plpgsql;

What would solve my problem is if there was a method to, at the end of
a begin/end block, I could rollback the changes made in that block
without having to raise an exception. Is it somehow possible to
explicitly rollback to one of these savepoints which are created by
begin/end blocks?

On Dec 19, 5:32 pm, "BigSmoke" <bigsm...@gmail.com> wrote:
> On Dec 19, 5:00 pm, "BigSmoke" <bigsm...@gmail.com> wrote:
>
>
>
> > On Dec 19, 4:16 pm, maili...@oopsware.de (Bernd Helmle) wrote:
>
> > > On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsm...@gmail.com> wrote:
>
> > > > I understand that due to a lack of nested transaction support, it is
> > > > not possible to use START TRANSACTION within a PL/PgSQL function. What
> > > > I, however, do not understand is why I can't use SAVEPOINT either. I'm
> > > > writing long test functions wherein, at the start of the function, I'd
> > > > like to define all test data followed by a "SAVEPOINT
> > > > fresh_test_data;". Will this become possible in the (near) future? I
> > > > mean, savepoints are of limited use to me if they imply that I can't
> > > > stick my tests in stored procedures.Use
>
> > > BEGIN
>
> > >   ...
>
> > > EXCEPTION
>
> > >   ...
>
> > > END;
>
> > > Blocks instead. The pl/pgsql exception handling is implemented on top
> > > of PostgreSQL's SAVEPOINT infrastructure. We are lacking user defined
> > > exception support, but you can raise generic errors with RAISE EXCEPTION.I can't solve my problem with a BEGIN
EXCEPTIONEND block because of 
> > what I do in these functions. Here's an example function.
>
> > CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
> > BEGIN
> >   -- Define some test data
>
> >   -- SAVEPOINT fresh_test_data; -- If only I could ...
>
> >   IF some_test_assertion_fails THEN
> >     RAISE EXCEPTION 'Some informative message';
> >   END IF;
>
> >   -- ROLLBACK TO SAVEPOINT fresh_test_data;
> > END;
> > $$ LANGUAGE plpgsql;
>
> > In these functions, I raise an exception whenever a test fails. Now, If
> > I want to create an implicit savepoint using BEGIN/END blocks around
> > individual tests, I don't see how I can still sanely preserve this
> > behavior without the most horrid of hacks. The following code is what I
> > think I would need to do to emulate savepoints without direct access to
> > them. :-(  (I hope that I'm missing something.)
>
> > CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$
> > BEGIN
> >   -- Define some test data
>
> >   BEGIN
> >     -- This is a useful test ;-)
> >     IF TRUE THEN
> >       RAISE EXCEPTION 'Aaargh! The test failed!';
> >     END IF;
>
> >     RAISE EXCEPTION '__dummy_restore_state__';
>
> >   EXCEPTION WHEN raise_exception THEN
> >     IF SQLERRM != '__dummy_restore_state__' THEN
> >       RAISE EXCEPTION '%', SQLERRM;
> >     END IF;
> >   END;
> > END;
> > $$ LANGUAGE plpgsql;What would solve my problem is if there was a method to, at the end of
> a begin/end block, I could rollback the changes made in that block
> without having to raise an exception. Is it somehow possible to
> explicitly rollback to one of these savepoints which are created by
> begin/end blocks?

I'm sorry for the sloppy English. Of course I meant to say "at the end
of a begin/end block, rollback" instead of "at the end of a begin/end
block, I could rollback". Hopefully, this didn't add in the confusion.

On 19 Dec 2006 08:37:01 -0800, "BigSmoke" <bigsmoke@gmail.com> wrote:

[...]

What would solve my problem is if there was a
> method to, at the end of
>> a begin/end block, I could rollback the changes made in that block
>> without having to raise an exception. Is it somehow possible to
>> explicitly rollback to one of these savepoints which are created by
>> begin/end blocks?
>
> I'm sorry for the sloppy English. Of course I meant to say "at the end
> of a begin/end block, rollback" instead of "at the end of a begin/end
> block, I could rollback". Hopefully, this didn't add in the confusion.

If you are hoping you could adopt exact Oracle behavior, i have to regret,
you have to do as you've already done. There's no such thing like 'explicit
transaction control' in plpgsql. Wouldn't it be easier to wrap your tests into
self-contained testfunctions like

SAVEPOINT A;
SELECT testcase1();
ROLLBACK TO A;
SELECT testcase2();
ROLLBACK TO A;
...

and to evaluate return codes into your application? Maybe someone knows better, but
that's the only solution that comes to my mind off-hand....

Bernd

On Dec 19, 7:03 pm, maili...@oopsware.de (Bernd Helmle) wrote:
> On 19 Dec 2006 08:37:01 -0800, "BigSmoke" <bigsm...@gmail.com> wrote:
>
> > What would solve my problem is if there was a
> > method to, at the end of a begin/end block,
> > rollback the changes made in that block
> > without having to raise an exception. Is it somehow possible to
> > explicitly rollback to one of these savepoints which are created by
> > begin/end blocks?
>
> If you are hoping you could adopt exact Oracle behavior, i have to regret,
> you have to do as you've already done. There's no such thing like 'explicit
> transaction control' in plpgsql. Wouldn't it be easier to wrap your tests into
> self-contained testfunctions like
>
> SAVEPOINT A;
> SELECT testcase1();
> ROLLBACK TO A;
> SELECT testcase2();
> ROLLBACK TO A;
> ...
>
> and to evaluate return codes into your application? Maybe someone knows better, but
> that's the only solution that comes to my mind off-hand....

Well, I'm already doing a full rollback after each test function in my
application, because I want test functions to be able to run
independently of each other. (I have multiple tests/assertions per test
function, which I why I wanted to use savepoints in these functions.)
But, I guess I _could_ make groups of tests with one test per function
where the functions in each group share some test data in the way you
describe. However, the sharing of test data would be cumbersome with
this method because I'd have to pass a lot of keys (with pointers to
the interesting rows in the test sets) to each test function.

I'll probably just stick with using tainted test data between the
individual tests in each test function. I guess I can't have
everything. ;-)

Thanks for your tips and time,

   - Rowan