Re: How do I save data and then raise an exception?

Gurjeet Singh wrote:
> On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com <mailto:alvherre(at)commandprompt(dot)com>> wrote:
>
> Rob Richardson wrote:
>
> > Here's what I need to do:
> >
> > IF query_check_fails THEN
> > UPDATE some_table SET some_value = 0 WHERE
> some_condition_is_true;
> > RAISE EXCEPTION 'Look, you idiot, do it right next time!';
> > END;
> >
> > I need the update to work, but I need to raise the exception so
> the C++
> > code recognizes the error. How can I do both?
>
> You need an autonomous transaction, which Postgres does not support
> directly but you can implement using dblink or a plperl function that
> connects back to the database.
>
>
> I was also going to suggest that but did not, because autonomous
> transaction won't help here! The data has been INSERTed or UPDATEd in
> this transaction, and hence won't be visible to the autonomous
> transaction, because the main transaction hasn't committed yet.

Autonomous transactions in the oracle sense would do the job perfectly.
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php

Faking that example with dblink isn't going to fly with PG - the select
with 10 rows before the rollback is never going to see 10.

For Rob's need though, running his update thru dblink it should do the
job. If the data he's fixing with the update statement was in the same
transaction, then the update wouldn't be needed at all and the whole
thing could just rollback. You have to assume that by the point where
Rob's code fires, the bad data is already committed. That update needs
to commit to undo that previous transaction, but he still needs to get
the 3rd party app to know that something went horribly wrong with its
insert.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au