Re: Unable to commit: transaction marked for rollback

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