From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Transactions and Exceptions
Date: Fri, 22 Jun 2007 14:50:37 +0200
Message-ID: <467BE1BB.A3DD.0030.0@indicator.be> (raw)
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?
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 += 1;
end;
end loop;
--if some records were skipped the calling application should know
--by the way, this function gets called through ADO like
--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.
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
Subject: Re: Transactions and Exceptions
In-Reply-To: <467BE1BB.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