From: | "BigSmoke" <bigsmoke(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Savepoints in PL/pgSQL |
Date: | 2006-12-19 16:00:01 |
Message-ID: | 1166544001.836287.258800@f1g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2006-12-19 16:02:09 | Re: Let's play bash the search engine |
Previous Message | Derrick Stensrud | 2006-12-19 15:52:58 | Re: Anyone? Best way to authenticate postgres against |