Re: Transactions and Exceptions

>>> Richard Huxton <dev(at)archonet(dot)com> 2007-06-22 15:24 >>>
>Bart Degryse wrote:
>> Dear all,
>> I'm having a problem with transactions and exceptions and need your advice.
>> I want a function to do two things:
>> - log something to a table (which is basically an insert)
>> - raise an exception under certain conditions
>> My problem is that when I raise the exception the insert is rolled back.
>> How can I work around that?
>
>Either:
>1. Not raising an exception, instead returning a status code to the
>application.
This is only an option when the message I want to return to the calling application is
static and when the calling application is capable of checking and processing a
returned status code. That way one also has to add more of the business logic in the
calling application, which is not always desired or possible.
Not an option for me in this case.

>2. Using dblink / dbi-link to reconnect to the database, which means
>your logging will take place in its own transaction.
This I like more. Though I don't use either dblink nor dbi-link, I do use this kind of
'double' connections already for other purposes in which transactions were never an
issue. So it never crossed my mind that I could use them for this too.
For the time being that's the way I'll walk. Thanks a lot!

Reading your suggestions I assume PostgreSQL lacks something like Oracle's
PRAGMA AUTONOMOUS_TRANSACTION
Shouldn't it be added, are there any plans in that direction? Returning a status code
is not always an option and using some dbi variant certainly isn't because of the
need for perlu. So then you're stuck?!
I can't imagine I'm the only one ever wanting to commit something and yet raise
an exception in the same function.