Re: Autocommit off - commits/rollbacks

On 03/14/2011 10:55 PM, Vogt, Michael wrote:
> Hey all
>
> I have a question, using the autocommit off option in postgres.
>
> As starting position I use a table called xxx.configuration using a
> unique id constraint.
>
> Why does postgres rollback the whole transaction after an error?

It's a PostgreSQL limitation (or, arguably, optimization). When a
statement fails, PostgreSQL doesn't have any way to return to the state
of the transaction just before that statement was executed. It aborts
the whole transaction. I usually find this is what I want anyway and
I'll be issuing an explicit ROLLBACK after the error anyway. I think
it's rather dangerous to allow a statement to execute successfully after
a prior one has failed, as to me it breaks the whole idea that a
transaction succeeds or fails as a whole.

If you want to handle errors, you can use a SAVEPOINT before a statement
that might fail, or you can use PL/PgSQL's EXCEPTION blocks.

--
Craig Ringer