>>> Richard Huxton <[EMAIL PROTECTED]> 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.