pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTransactions and Exceptions
5+ messages / 2 participants
[nested] [flat]
* Transactions and Exceptions
@ 2007-06-22 12:50 Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 5+ messages in thread
From: Bart Degryse @ 2007-06-22 12:50 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Transactions and Exceptions
@ 2007-06-22 13:24 Richard Huxton <dev@archonet.com>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-06-22 13:24 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Transactions and Exceptions
@ 2007-06-22 13:52 Bart Degryse <Bart.Degryse@indicator.be>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 2 replies; 5+ messages in thread
From: Bart Degryse @ 2007-06-22 13:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
>>> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Transactions and Exceptions
@ 2007-06-22 17:00 Richard Huxton <dev@archonet.com>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-06-22 17:00 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Transactions and Exceptions
@ 2007-06-26 07:26 Bart Degryse <Bart.Degryse@indicator.be>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 5+ messages in thread
From: Bart Degryse @ 2007-06-26 07:26 UTC (permalink / raw)
To: pgsql-sql
>>>> 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
>>> 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
You're right of course about dblink. We never installed it though because it
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
in the source database, which is too much if you only need say 8 tables of the 300
that exist. So we have chosen to make our own 'interface' having just what we need
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
equivalent to PRAGMA AUTONOMOUS_TRANSACTION would be nice to have.
Thanks for your help and insight Richard.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-06-26 07:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-22 12:50 Transactions and Exceptions Bart Degryse <Bart.Degryse@indicator.be>
2007-06-22 13:24 ` Richard Huxton <dev@archonet.com>
2007-06-22 13:52 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-06-22 17:00 ` Richard Huxton <dev@archonet.com>
2007-06-26 07:26 ` Bart Degryse <Bart.Degryse@indicator.be>
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