Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id D362F9FBB76
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 10:52:50 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 59483-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 10:52:38 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id 591EB9FC0F5
	for <pgsql-sql@postgresql.org>; Fri, 22 Jun 2007 10:52:46 -0300 (ADT)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Fri, 22 Jun 2007 15:37:23 +0200
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Fri, 22 Jun 2007 15:52:43 +0200
Message-Id: <467BF045.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Fri, 22 Jun 2007 15:52:37 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: "Richard Huxton" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Transactions and Exceptions
References: <467BE1BB.A3DD.0030.0@indicator.be><467BCDA1.9070606@archonet.co
 m>
In-Reply-To: <467BCDA1.9070606@archonet.com>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__PartB4938435.1__="
X-imss-version: 2.047
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.308 tagged_above=0 required=5 tests=AWL=0.170,
	BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 200706/174
X-Sequence-Number: 28664

--=__PartB4938435.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

>>> 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:=20
>> - 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=20
>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=20
calling application, which is not always desired or possible.=20
Not an option for me in this case.

>2. Using dblink / dbi-link to reconnect to the database, which means=20
>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=20
'double' connections already for other purposes in which transactions were =
never an=20
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!
=20
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=20
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.

--=__PartB4938435.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-15=
">
<META content=3D"MSHTML 6.00.2900.2180" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>&gt;&gt;&gt; Richard Huxton &lt;dev@archonet.com&gt; 2007-06-22 15:24 =
&gt;&gt;&gt;<BR>&gt;Bart Degryse wrote:<BR>&gt;&gt; Dear all,<BR>&gt;&gt; =
I'm having a problem with transactions and exceptions and need your =
advice.<BR>&gt;&gt; I want a function to do two things: <BR>&gt;&gt; - log =
something to a table (which is basically an insert)<BR>&gt;&gt; - raise an =
exception under certain conditions<BR>&gt;&gt; My problem is that when I =
raise the exception the insert is rolled back.<BR>&gt;&gt; How can I work =
around that?<BR>&gt;<BR>&gt;Either:<BR>&gt;1. Not raising an exception, =
instead returning a status code to the <BR>&gt;application.</DIV>
<DIV>This is only an option when the message I want to return to the =
calling application is</DIV>
<DIV>static and when the calling application is capable of checking and =
processing a</DIV>
<DIV>returned status code. That way one also has to add more of the =
business logic in the </DIV>
<DIV>calling application, which is not always desired or possible. </DIV>
<DIV>Not an option for me in this case.</DIV>
<DIV><BR>&gt;2. Using dblink / dbi-link to reconnect to the database, =
which means <BR>&gt;your logging will take place in its own transaction.<BR=
>This I like more. Though I don't use either dblink nor dbi-link, I do use =
this kind of </DIV>
<DIV>'double' connections already for other purposes in which transactions =
were never an </DIV>
<DIV>issue. So it never crossed my mind that I could use them for this =
too.</DIV>
<DIV>For the time being that's the way I'll walk. Thanks a lot!</DIV>
<DIV>&nbsp;</DIV>
<DIV>Reading your suggestions I assume PostgreSQL lacks something like =
Oracle's</DIV>
<DIV>PRAGMA AUTONOMOUS_TRANSACTION</DIV>
<DIV>Shouldn't it be added, are there any plans in that direction? =
Returning a status code</DIV>
<DIV>is not always an option and using some dbi variant certainly isn't =
because of the </DIV>
<DIV>need for perlu. So then you're stuck?!</DIV>
<DIV>I can't imagine I'm the only one ever wanting to commit something and =
yet raise</DIV>
<DIV>an exception in the same function.</DIV></BODY></HTML>

--=__PartB4938435.1__=--