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__=--