Recording exceptions within function (autonomous transactions?)

From: Steve Pritchard <steve(dot)pritchard(at)bto(dot)org>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Recording exceptions within function (autonomous transactions?)
Date: 2015-10-06 09:38:25
Message-ID: CAF7AqmzzHB_WugArV4JGgUZMu8+PqQzkpCeXuHZsg4oSrt7PrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am porting several stored procedures from Oracle to Postgres. In the
Oracle code, if an exception is thrown within a stored procedure, the
exception is caught and details are written to a database table using an
autonomous transaction (as the main transaction is rolled back).

As far as I can see from the documentation, Postgres doesn't
support autonomous transaction (although there is talk about it at
https://wiki.postgresql.org/wiki/Autonomous_subtransactions - is this
something that is being discussed for a future release?).

The Postgres functions that I'm writing are batch processes that will be
invoked via a scheduler (either cron or pgAgent).

Ideally I'd like to record the exceptions in a database table. If this
isn't possible then recording in a log fie would be acceptable, but I'd
like to keep this separate from the main postgres log.

Alternatives that I've come up with (none of them very satisfactory):

- use 'raise' to record in postgres log
- put the error recording in the client code (as invoked by scheduler) -
use BEGIN TRANSACTION to start a new transaction
- use COPY to output to a file

Can anyone suggest something that would meet my requirements above?

Steve Pritchard
British Trust for Ornithology, UK

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Mead 2015-10-06 10:37:25 Re: Recording exceptions within function (autonomous transactions?)
Previous Message Albe Laurenz 2015-10-06 07:33:43 Re: Selecting pairs of numbers