Re: Savepoints in PL/pgSQL

On Dec 19, 5:00 pm, "BigSmoke" <bigsm(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On Dec 19, 4:16 pm, maili(dot)(dot)(dot)(at)oopsware(dot)de (Bernd Helmle) wrote:
>
>
>
> > On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsm(dot)(dot)(dot)(at)gmail(dot)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;

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?