Thread: Recording exceptions within function (autonomous transactions?)
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
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
Scott Mead
Sr. Architect
OpenSCG
Steve PritchardBritish Trust for Ornithology, UK
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
Scott Mead <scottm@openscg.com> writes:
>> On Oct 6, 2015, at 05:38, Steve Pritchard <steve.pritchard@bto.org> wrote:
>> [ how to fake an autonomous transaction? ]
> It's hacky, and, I haven't tried it in a few years. Setup a foreign table that resides in the same database. When
youwrite to the foreign table, it will be using a 'loopback' connection, and that transaction will be able to commit
becauseit 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.
My recollection is that you can do this with dblink, but *not* with FDWs
--- or at least, not with postgres_fdw. The latter is smart enough to
roll back your remote transaction when the local one rolls back.
regards, tom lane
On 10/6/2015 2: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).
we were faced with a similar task a few years ago, massive complex system with 100s of stored procedures written in Oracle's PL/SQL. We opted to completely rebase the business logic software in a conventional high level language (Java), and only used pl/pgsql functions when they had significant performance enhancements. This Java version was designed to work with either Oracle or Postgres, and in fact performs BETTER than the original version, and is easier to maintain.
I know thats probably not what you wanted to hear, but I'm just throwing it out there.
-- john r pierce, recycling bits in santa cruz