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
On Oct 6, 2015, at 05:38, Steve Pritchard <steve.pritchard@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
Show quoted text
Steve Pritchard
British 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 fileCan 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:
Then create a handler statement that writes the exception out.
Steve Pritchard
British Trust for Ornithology, UK
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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