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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
