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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
