From Bart.Degryse@indicator.be Fri Jun  22 12:50:52 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 512109FC1DA
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 09:50:52 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 32487-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 09:50:47 -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 AB3B19FB905
	for <pgsql-sql@postgresql.org>; Fri, 22 Jun 2007 09:50:47 -0300 (ADT)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Fri, 22 Jun 2007 14:35:25 +0200
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Fri, 22 Jun 2007 14:50:45 +0200
Message-Id: <467BE1BB.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Fri, 22 Jun 2007 14:50:37 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: Transactions and Exceptions
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__Part1D3A2D8D.0__="
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.495 tagged_above=0 required=5 tests=AWL=-0.017,
	BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_30_40=0.374,
	HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 200706/172
X-Sequence-Number: 28662

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

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?
=20
Example (pseudocode)
CREATE OR REPLACE FUNCTION public.test() RETURNS void AS
$body$
DECLARE
  num integer;
BEGIN
  --log start of function
  insert into logtable(fieldX, fieldY, fieldZ) values ('test', null, =
'start');
  --process some 2,500,000 records
  --srffuntion is a plperlu function that fetches records from eg Oracle =
using DBI
  for rec in select * from srffunction loop
    begin
      insert into targettable(field1, ..., fieldN) values (rec.field1, =
..., rec.fieldN);
    exception
      when others
        --log why this record could not be inserted
        insert into logtable(fieldX, fieldY, fieldZ) values ('test', =
rec.id, SQLERRM);
        num +=3D 1;
    end;
  end loop;
  --if some records were skipped the calling application should know
  --by the way, this function gets called through ADO like=20
  --conn.execute('select test()',,adCmdText)
  if num then
    raise exception '% records skipped', num;
  end if;
  --log end of function
  insert into logtable(fieldX, fieldY, fieldZ) values ('test', null, =
'end');
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
So I want to use 'raise exception' as the way to inform the calling =
application that something went wrong
but the inserts that have been done are ok and thus must be committed =
instead of rolled back by the
'raise exception'. How can I do that?
Thanks for any advice or ideas.

--=__Part1D3A2D8D.0__=
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>Dear all,</DIV>
<DIV>I'm having a problem with transactions and exceptions and need your =
advice.</DIV>
<DIV>I want a function to do two things: </DIV>
<DIV>- log something to a table (which is basically an insert)</DIV>
<DIV>- raise an exception under certain conditions</DIV>
<DIV>My problem is that when I raise the exception the insert is rolled =
back.</DIV>
<DIV>How can I work around that?</DIV>
<DIV>&nbsp;</DIV>
<DIV>Example (pseudocode)</DIV>
<DIV>CREATE OR REPLACE FUNCTION public.test() RETURNS&nbsp;void AS<BR>$body=
$</DIV>
<DIV>DECLARE</DIV>
<DIV>&nbsp; num integer;<BR>BEGIN</DIV>
<DIV>&nbsp; --log start of function</DIV>
<DIV>&nbsp; insert into logtable(fieldX, fieldY, fieldZ) values ('test', =
null, 'start');</DIV>
<DIV>&nbsp; --process some 2,500,000 records</DIV>
<DIV>&nbsp; --srffuntion is a plperlu function that fetches records from =
eg Oracle using DBI</DIV>
<DIV>&nbsp; for rec in select&nbsp;* from srffunction loop</DIV>
<DIV>&nbsp;&nbsp;&nbsp; begin</DIV>
<DIV>&nbsp; &nbsp;&nbsp;&nbsp; insert into targettable(field1, ..., =
fieldN) values (rec.field1, ..., rec.fieldN);</DIV>
<DIV>&nbsp;&nbsp;&nbsp; exception</DIV>
<DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when others</DIV>
<DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --log why this record =
could not be inserted</DIV>
<DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; insert into logtable(fieldX=
, fieldY, fieldZ) values ('test', <FONT face=3D"Courier New">rec.id</FONT>,=
 <FONT face=3D"Courier New">SQLERRM</FONT>);</DIV>
<DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; num +=3D 1;</DIV>
<DIV>&nbsp;&nbsp;&nbsp; end;</DIV>
<DIV>&nbsp; end loop;</DIV>
<DIV>&nbsp; --if some records were skipped the calling application should =
know</DIV>
<DIV>&nbsp; --by the way, this function gets called through ADO like =
</DIV>
<DIV>&nbsp; --conn.execute('select test()',,adCmdText)</DIV>
<DIV>&nbsp; if num then</DIV>
<DIV>&nbsp;&nbsp;&nbsp; raise exception '% records skipped', num;</DIV>
<DIV>&nbsp; end if;</DIV>
<DIV>&nbsp; --log end of function<BR>
<DIV>&nbsp; insert into logtable(fieldX, fieldY, fieldZ) values ('test', =
null, 'end');</DIV>END;<BR>$body$<BR>LANGUAGE 'plpgsql' VOLATILE CALLED ON =
NULL INPUT SECURITY INVOKER;<BR></DIV>
<DIV>So I want to use 'raise exception' as the way to inform the calling =
application that something went wrong</DIV>
<DIV>but the inserts that have been done are ok and thus must be committed =
instead of rolled back&nbsp;by the</DIV>
<DIV>'raise exception'. How can I do that?</DIV>
<DIV>Thanks for any advice or ideas.</DIV></BODY></HTML>

--=__Part1D3A2D8D.0__=--




From dev@archonet.com Fri Jun  22 13:25:00 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A51959FC1E1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 10:25:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 12494-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 10:24:52 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id D7B0D9FC227
	for <pgsql-sql@postgresql.org>; Fri, 22 Jun 2007 10:24:51 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 23C3C763D7;
	Fri, 22 Jun 2007 14:24:50 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 0B70C274120;
	Fri, 22 Jun 2007 14:24:50 +0100 (BST)
Message-ID: <467BCDA1.9070606@archonet.com>
Date: Fri, 22 Jun 2007 14:24:49 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Bart Degryse <Bart.Degryse@indicator.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Transactions and Exceptions
References: <467BE1BB.A3DD.0030.0@indicator.be>
In-Reply-To: <467BE1BB.A3DD.0030.0@indicator.be>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/173
X-Sequence-Number: 28663

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.

2. Using dblink / dbi-link to reconnect to the database, which means 
your logging will take place in its own transaction.

-- 
   Richard Huxton
   Archonet Ltd



From Bart.Degryse@indicator.be Fri Jun  22 13:52:50 2007
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__=--




From dev@archonet.com Fri Jun  22 17:00:16 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8EF7A9FC1F6
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 14:00:16 -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 03514-01-5 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 22 Jun 2007 14:00:04 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id B289C9FB975
	for <pgsql-sql@postgresql.org>; Fri, 22 Jun 2007 14:00:05 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 514B674116;
	Fri, 22 Jun 2007 18:00:02 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id EA50E274154;
	Fri, 22 Jun 2007 18:00:01 +0100 (BST)
Message-ID: <467C0011.8000805@archonet.com>
Date: Fri, 22 Jun 2007 18:00:01 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Bart Degryse <Bart.Degryse@indicator.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Transactions and Exceptions
References: <467BE1BB.A3DD.0030.0@indicator.be><467BCDA1.9070606@archonet.co
	m>
	<467BF045.A3DD.0030.0@indicator.be>
In-Reply-To: <467BF045.A3DD.0030.0@indicator.be>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/177
X-Sequence-Number: 28667

Bart Degryse wrote:
>> 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

It might well be a useful feature (particularly for logging-style 
interactions as you have). I'm not sure it's particularly simple to do 
(other than by having a whole new backend as dblink will give you).

> 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?!

Well, dblink is pure C iirc. I think the advantage with dbi-link is that 
you can connect to any type of database DBI can.

-- 
   Richard Huxton
   Archonet Ltd



From Bart.Degryse@indicator.be Tue Jun  26 07:26:31 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 95C139FB544
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 26 Jun 2007 04:26:31 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 64030-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 26 Jun 2007 04:26:25 -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 ECAF49FB4FE
	for <pgsql-sql@postgresql.org>; Tue, 26 Jun 2007 04:26:26 -0300 (ADT)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Tue, 26 Jun 2007 09:10:59 +0200
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Tue, 26 Jun 2007 09:26:25 +0200
Message-Id: <4680DBBB.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Tue, 26 Jun 2007 09:26:19 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: Re: Transactions and Exceptions
References: <467BE1BB.A3DD.0030.0@indicator.be><467BCDA1.9070606@archonet.co
	m><467BF045.A3DD.0030.0@indicator.be><467BF045.A3DD.0030.0@indicator.be><
	467C0011.8000805@archonet.com>
In-Reply-To: <467C0011.8000805@archonet.com>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__Part9ABD518B.0__="
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.209 tagged_above=0 required=5 tests=AWL=0.257,
	BAYES_40=-0.185, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 200706/212
X-Sequence-Number: 28702

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

>>>> Richard Huxton <dev@archonet.com> 2007-06-22 19:00 >>>
>Bart Degryse wrote:
>>> 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
>
>It might well be a useful feature (particularly for logging-style=20
>interactions as you have). I'm not sure it's particularly simple to do=20
>(other than by having a whole new backend as dblink will give you).
>
>> 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?!
>
>Well, dblink is pure C iirc. I think the advantage with dbi-link is =
that=20
>you can connect to any type of database DBI can.
>
>--=20
>   Richard Huxton
>   Archonet Ltd
You're right of course about dblink. We never installed it though because =
it=20
lacks the ability to connect to non-postgresql databases. dbi-link on the =
other
hand didn't make it either because of the overhead. It basically duplicates=
 all tables=20
in the source database, which is too much if you only need say 8 tables of =
the 300=20
that exist. So we have chosen to make our own 'interface' having just what =
we need=20
and nothing more. Using it to have our logging system in a seperate =
transaction is
perfectly possible, we just didn't think of it ourselfs. Nevertheless I =
think an=20
equivalent to PRAGMA AUTONOMOUS_TRANSACTION would be nice to have.
Thanks for your help and insight Richard.
=20

--=__Part9ABD518B.0__=
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.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>&gt;&gt;&gt;&gt; Richard Huxton &lt;dev@archonet.com&gt; 2007-06-22 =
19:00 &gt;&gt;&gt;<BR>&gt;Bart Degryse wrote:<BR>&gt;&gt;&gt; 2. Using =
dblink / dbi-link to reconnect to the database, which means <BR>&gt;&gt;&gt=
; your logging will take place in its own transaction.<BR>&gt;&gt; This I =
like more. Though I don't use either dblink nor dbi-link, I do use this =
kind of <BR>&gt;&gt; 'double' connections already for other purposes in =
which transactions were never an <BR>&gt;&gt; issue. So it never crossed =
my mind that I could use them for this too.<BR>&gt;&gt; For the time being =
that's the way I'll walk. Thanks a lot!<BR>&gt;&gt;&nbsp; <BR>&gt;&gt; =
Reading your suggestions I assume PostgreSQL lacks something like =
Oracle's<BR>&gt;&gt; PRAGMA AUTONOMOUS_TRANSACTION<BR>&gt;<BR>&gt;It might =
well be a useful feature (particularly for logging-style <BR>&gt;interactio=
ns as you have). I'm not sure it's particularly simple to do <BR>&gt;(other=
 than by having a whole new backend as dblink will give you).<BR>&gt;<BR>&g=
t;&gt; Shouldn't it be added, are there any plans in that direction? =
Returning a status code<BR>&gt;&gt; is not always an option and using some =
dbi variant certainly isn't because of the <BR>&gt;&gt; need for perlu. So =
then you're stuck?!<BR>&gt;<BR>&gt;Well, dblink is pure C iirc. I think =
the advantage with dbi-link is that <BR>&gt;you can connect to any type of =
database DBI can.<BR>&gt;<BR>&gt;-- <BR>&gt;&nbsp;&nbsp; Richard Huxton<BR>=
&gt;&nbsp;&nbsp; Archonet Ltd<BR></DIV>
<DIV>You're right of course about dblink. We never installed it though =
because it </DIV>
<DIV>lacks the ability to connect to non-postgresql databases. dbi-link on =
the other</DIV>
<DIV>hand didn't make it either because of the overhead. It basically =
duplicates all tables </DIV>
<DIV>in the source database, which is too much if you only need say 8 =
tables of the 300 </DIV>
<DIV>that exist. So we have chosen to make our own 'interface' having just =
what we need </DIV>
<DIV>and nothing more. Using it to have our logging system in a seperate =
transaction is</DIV>
<DIV>perfectly possible, we just didn't think of it ourselfs. Nevertheless =
I think an </DIV>
<DIV>equivalent to PRAGMA AUTONOMOUS_TRANSACTION would be nice to =
have.</DIV>
<DIV>Thanks for your help and insight Richard.</DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

--=__Part9ABD518B.0__=--