Re: Autocommit off - commits/rollbacks

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? I
compared the behavior with oracle/hsql - those dbms commit whats
possible. To illustrate my question, here are some examples:

Here is the postgres example
---
nasdb=# \set AUTOCOMMIT 'off'
nasdb=# insert into xxx.configuration(name, value, id) VALUES('aa',
'mm', 812); INSERT 0 1 nasdb=# insert into xxx.configuration(name,
value, id) VALUES('aa', 'mm', 813); INSERT 0 1 nasdb=# insert into
xxx.configuration(name, value, id) VALUES('aa', 'mm', 812);
ERROR: duplicate key value violates unique constraint
"configuration_pk"
nasdb=# commit;
ROLLBACK

>> value with id 812 and 813 are not stored in the table due the
>> rollback
---

Here is the same function using oracle:
---
SQL> set auto off;
SQL> insert into xxx.configuration(id,name,value) values(200,'aa','bb');
1 row created.
SQL> insert into xxx.configuration(id,name,value) values(201,'aa','bb');
1 row created.
SQL> insert into xxx.configuration(id,name,value) values(201,'aa','bb');
insert into xxx.configuration(id,name,value) values(201,'aa','bb')
*
ERROR at line 1:
ORA-00001: unique constraint (XXX.CONFIGURATION_PK) violated
SQL> commit;
Commit complete.

>> the first two inserts (id 200 and 201) are stored in the
xxx.configuration table.
---

Also hsqldb will insert all possible data into the db (like oracle).

Or can postgres behavior be changed to a "commit whatever is possible"
mode?

Regards
michu

On Mon, Mar 14, 2011 at 03:55:37PM +0100, Vogt, Michael wrote:
> Why does postgres rollback the whole transaction after an error? I
> compared the behavior with oracle/hsql - those dbms commit whats
> possible.

A transaction is supposed to commit or rollback. If you want to hold
on to something that you've done and then try something (and pursue
alternatives depending on whether you get an error), use a savepoint.
See
http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

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

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:

> 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

Well, any transactional RDBMS whatsoever should behave that way. Hardly
a PostgreSQL exclusive feature :-)

--
Jerry Sievers
e: gsievers19(at)comcast(dot)net
p: 305.321.1144

Yes We can use exception for each statement to restrict the rollback.
But how we can use SAVEPOINT and rollback to SAVEPOINT
in the stored function or procedure in POSTGRES?
We can only use the savepoints in transactions but not in stored functions.

Regards,
Tushar

On Tue, Mar 29, 2011 at 12:54 PM, Jerry Sievers <gsievers19(at)comcast(dot)net>wrote:

> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
>
> > 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
>
> Well, any transactional RDBMS whatsoever should behave that way. Hardly
> a PostgreSQL exclusive feature :-)
>
> --
> Jerry Sievers
> e: gsievers19(at)comcast(dot)net
> p: 305.321.1144
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Tuesday, March 29, 2011 4:35:04 am tushar nehete wrote:
> Yes We can use exception for each statement to restrict the rollback.
> But how we can use SAVEPOINT and rollback to SAVEPOINT
> in the stored function or procedure in POSTGRES?
> We can only use the savepoints in transactions but not in stored functions.
>
> Regards,
> Tushar
>

See below for how to achieve the same effect using EXCEPTIONS in pl/pgsql:

http://www.postgresql.org/docs/9.0/interactive/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com