From: Bart Degryse <Bart.Degryse@indicator.be>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Transactions and Exceptions
Date: Fri, 22 Jun 2007 15:52:37 +0200
Message-ID: <467BF045.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <467BCDA1.9070606@archonet.com>
References: <467BE1BB.A3DD.0030.0@indicator.be>
<467BCDA1.9070606@archonet.co m>
>>> Richard Huxton <dev@archonet.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be, dev@archonet.com
Subject: Re: Transactions and Exceptions
In-Reply-To: <467BF045.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox