Re: Exception handling in plperl

On Tue, Mar 13, 2007 at 11:23:03PM -0400, Jasbinder Singh Bali wrote:
> I have a stored procedure written in plperl.
> This procedure has series of inserts at various levels. A few inserts on
> certain tables invoke triggers
> that launch tools outside the domain of the database.
>
> How can I make everything as one single transaction and simply roll back
> everything whenever an exception occurs.

Statements are always executed in a transaction; if you're not
inside an explicit transaction block then statements will be
implicitly wrapped in a transaction for you. If the outermost
statement is "SELECT function_that_does_inserts()" then everything
that happens inside that function is part of the same transaction,
and if any of the function's statements fail then the entire
transaction will fail unless you trap the error. In plperlu you
can trap errors with "eval"; see the Perl documentation for more
information.

Are you wanting to trap errors so you can roll back actions that
happened outside the database? If so then you could use eval to
handle failures, then do whatever cleanup needs to be done (and can
be done) outside the database, then use elog to raise an error and
make the current transaction fail. However, if this is what you're
trying to do then understand that actions outside the database
aren't under the database's transaction control and might not be
able to be rolled back.

If I've misunderstood what you're asking then please provide more
information about what you're trying to do.

--
Michael Fuhr