Re: Savepoints in PL/pgSQL - Mailing list pgsql-general

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.