Skip site navigation (1) Skip section navigation (2)

Re: Unable to commit: transaction marked for rollback

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Radosław Smogura <rsmogura(at)softperience(dot)eu>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Unable to commit: transaction marked for rollback
Date: 2010-07-04 12:52:44
Message-ID: 4C30841C.9070604@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
On 04/07/10 17:59, Radosław Smogura wrote:
> Unfortunatly in my apps I'm using JPA bridges and I don't belive that those 
> use savepoints. Bad in this is that if I do mass processing it's obvious that 
> some "records" can be badly processed, and I don't want to break transaction, 
> (much more with JPA I can't use savepoints because there is no fixed point of 
> send changes to database). I just want to write in this transaction 
> information to 2nd table or mark record as failed.

Your best bet in that situation is not to insert bad data in the first
place, but I know that's not always possible. Really, all you can do is
do each change that might fail in its own transaction, and if it fails
discard and regenerate the object graph you tried to commit.

You might think that you could perform an explicit flush wrapped in a
savepoint after each change, but the JPA state of the objects you're
using would still be messed up and you'd need to discard the
half-committed objects and re-do anyway. You'd have things like
oplock/version fields inconsistent with the database state.

On the occasions I need to do anything like this, I clone a copy of my
to-be-committed object graph  (  SerializationHelper is really useful
for this  ) and, if the JPA transaction fails, I restore the saved copy
of my object graph in place of the half-committed broken objects left by
JPA.

PostgreSQL's design - killing the whole transaction when something goes
wrong - is a really good fit for JPA, because you cannot generally
recover from an error in JPA without re-doing your transaction, no
matter what database you are using.

> Yesterday I realised that my application can not work as I suspected. I need 
> to test it for this special case. In any way I think about simple and special 
> patch, just and only for JPA bridges to add SAVEPOINT before each query.

Given that a JPA implementation may choose to flush changes in various
ways, including multi-valued inserts, I be really nervous about that.

How would you report back to the app that some data was ignored, anyway?
The changes were applied some time ago, and have only just been flushed.
AFAIK JPA doesn't provide any kind of error listener or callback
mechanism, and I wouldn't want to trust to SQL warnings for something as
important as ignored changes.

Now, the JPA provider could use savepoints internally to ignore certain
kinds of error, translating them to warnings it reported ... somehow ...
but that seems like an exceedingly ugly thing to do. It's hard for the
JPA provider to tell the difference between a "minor" error and a really
bad one, especially as the definition of those things changes depending
on the app.

BTW: Please don't top post.

--
Craig Ringer

In response to

pgsql-jdbc by date

Next:From: Sumit PandyaDate: 2010-07-05 10:59:20
Subject: Interpretability with xDBC specification
Previous:From: Thomas KellererDate: 2010-07-04 11:25:07
Subject: Re: Picking up strange queries for "pg_catalog"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group