> On Oct 6, 2015, at 05:38, Steve Pritchard <steve.pritch...@bto.org> 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? It's hacky, and, I haven't tried it in a few years. Setup a foreign table that resides in the same database. When you write to the foreign table, it will be using a 'loopback' connection, and that transaction will be able to commit because it is a separate connection.
To be fair, I haven't actually done this since the days of dblink, I *believe* it should work with fdw though. -- Scott Mead Sr. Architect OpenSCG http://openscg.com > Steve Pritchard > British Trust for Ornithology, UK
