Hi,
I know this issue has been discussed at length before, but postgresql's
behaviour of forcing a rollback when any error occurs is making life
very difficult for me. We use Spring's transaction proxies, which are
applied to methods in web controllers. In the backend code, if a runtime
exception occurs and we let it propagate, then Spring will catch it and
roll back the transaction for us. However, sometimes we need to catch
exceptions on certain ops that are allowed to fail, and let the rest of
the transaction proceed.
For example, in our app there may be many operations that are performed
in the course of a single web request: changing system state, updating
last request times, writing logs records, etc. Normally if we catch the
exception, other dbs (Oracle, MSSQL) will let us keep going. However
with postgresql, if something goes wrong that we would normally ALLOW,
it bombs the whole request! This is no good! :(
What has effectively happened is that postgresql has taken away my right
to allow certain non-fatal SQL errors to occur during a transactional
request. For example, if something goes wrong, I can't even write an
event row to our auditing table! Argh!! We do not have the option to
turn off the transactions at the top level; this would require a major
rework, and require us to put manual transaction blocks everywhere -
exactly what Spring helps us avoid.
Is there some way to turn this behaviour off without having to resort to
pg-specific code (ie sprinkling checkpoints everywhere)? We allow
customers to plug their chosen db into our app for a backend. This has
been fine until someone asked for postgresql... this behaviour is
different to the other big dbs. Is there any reason this behaviour is
not at least optional?
Thanks for listening. My research on other threads has not given me much
hope, but maybe things have changed recently?
--
Tyson Lloyd Thwaites
Lead Software Developer
Allianza Services Pty Ltd
M: 0404 456 858
P: (+618) 8232 5525
F: (+618) 8232 8252
W: www.allianza.com.au
