Обсуждение: Transactions and Exceptions

Поиск
Список
Период
Сортировка

Transactions and Exceptions

От
"Bart Degryse"
Дата:
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.

Re: Transactions and Exceptions

От
Richard Huxton
Дата:
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

Re: Transactions and Exceptions

От
"Bart Degryse"
Дата:
>>> 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.

Re: Transactions and Exceptions

От
Richard Huxton
Дата:
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

Re: Transactions and Exceptions

От
"Bart Degryse"
Дата:
>>>> 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.