From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | "Vogt, Michael" <Michael(dot)Vogt(at)united-security-providers(dot)ch> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Autocommit off - commits/rollbacks |
Date: | 2011-03-29 02:25:56 |
Message-ID: | 4D914334.5050203@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2011-03-29 02:28:41 | Re: Values larger than 1/3 of a buffer page cannot be indexed. |
Previous Message | Merlin Moncure | 2011-03-28 20:09:38 | Re: can a function have a setof (returned from another function) as input |