Re: Recording exceptions within function (autonomous transactions?)

On 10/06/2015 02:38 AM, Steve Pritchard wrote:
> 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?

You do not say what language you are using for the procedures, assuming
plpgsql have you looked at:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Then create a handler statement that writes the exception out.

>
> Steve Pritchard
> British Trust for Ornithology, UK


--
Adrian Klaver
adrian.klaver@aklaver.com