Re: Unable to commit: transaction marked for rollback

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.

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.

Dnia niedziela 04 lipiec 2010 o 04:20:22 Craig Ringer napisał(a):
> On 03/07/10 16:36, Radosław Smogura wrote:
> > I've looked at error from your example, because I didn't belived such
> > behavior. Unfortunatly it's true. Postgresql disallow to execute next
> > statement if error occured - this is realy bad approach, because error
> > can be from dupliacte keys etc. :(
>
> If you want to handle errors and continue, use a subtransaction with
> BEGIN SAVEPOINT and ROLLBACK TO SAVEPOINT.
>
> Most of the time it's a performance advantage - and perfectly suitable
> for apps - to avoid keeping a rollback point for each statement, and
> instead chuck the whole transaction away if something goes wrong. Most
> apps expect to retry the whole transaction if anything goes wrong
> anyway, since it's often hard to tell if the issue can be resolved by
> retrying just one (possibly altered) statement or not.
>
> It's also safer. If Pg allowed a transaction to continue after an INSERT
> failed, it'd end up committing an incomplete set of the requested
> changes and hoping that the user/app noticed. I don't like that - I'd
> much rather have to explicitly handle the issue, and otherwise have an
> error in a transaction be an error that aborts the transaction, not just
> the particular statement that caused the error.
>
> For bulk insertion, you're almost always better off copying the data
> into a temporary table, cleaning it up if necessary, then using INSERT
> INTO ... SELECT to insert it duplicate-free.
>
> I guess an optional mode that issued an implicit savepoint before each
> transaction and allowed "ROLLBACK TO LAST STATEMENT" would be nice,
> though, as a convenience to avoid all the savepoint management on the
> rare occasions when you do want statement-level rollback.
>
> --
> Craig Ringer