Thread: Autocommit off - commits/rollbacks

Autocommit off - commits/rollbacks

From

"Vogt, Michael"

Date:

14 March 2011, 15:15:02

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

Attachment

smime.p7s

Re: Autocommit off - commits/rollbacks

From

Alexander Pyhalov

Date:

14 March 2011, 16:27:58

Hello.
On 03/14/2011 12:24, Vogt, Michael wrote:
> 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:
....
AFAIK, sqlplus uses savepoints behind the scene. So, you can do
something like this:

 > create table t1 (i int unique);
CREATE TABLE
 > begin;
BEGIN
*> insert into t1 values(1);
INSERT 0 1
*> savepoint s1;
SAVEPOINT
*> insert into t1 values(2);
INSERT 0 1
*> savepoint s2;
SAVEPOINT
*> insert into t1 values(2);
ERROR:  duplicate key value violates unique constraint "t1_i_key"
!> ROLLBACK TO SAVEPOINT s2;
ROLLBACK
*> commit;
COMMIT

Or, you can just do:
 > \set ON_ERROR_ROLLBACK on


--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University

Re: Autocommit off - commits/rollbacks

From

"David Johnston"

Date:

14 March 2011, 21:47:47

Set autocommit to "true/on".  That will give you the desired behavior of
allowing all those things that succeed to remain committed.
David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Vogt, Michael
Sent: Monday, March 14, 2011 5:24 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Autocommit off - commits/rollbacks

Hey all

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

Regards
michu

Re: Autocommit off - commits/rollbacks

From

"Vogt, Michael"

Date:

15 March 2011, 11:33:18

Thanks alexander

The on_error_rollback setting was exactly the switch I was looking for, thanks. However I tried to set this setting via
thejdbc driver (connection object) or jdbc properties, but couldnt find a switch to enable this option. 

How can i set this setting from my java app?

Regards and thanks in advance
michael

-----Ursprüngliche Nachricht-----
Von: Alexander Pyhalov [mailto:alp@rsu.ru]
Gesendet: Montag, 14. März 2011 17:28
An: Vogt, Michael
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Autocommit off - commits/rollbacks

Hello.
On 03/14/2011 12:24, Vogt, Michael wrote:
> 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:
....
AFAIK, sqlplus uses savepoints behind the scene. So, you can do something like this:

 > create table t1 (i int unique);
CREATE TABLE
 > begin;
BEGIN
*> insert into t1 values(1);
INSERT 0 1
*> savepoint s1;
SAVEPOINT
*> insert into t1 values(2);
INSERT 0 1
*> savepoint s2;
SAVEPOINT
*> insert into t1 values(2);
ERROR:  duplicate key value violates unique constraint "t1_i_key"
!> ROLLBACK TO SAVEPOINT s2;
ROLLBACK
*> commit;
COMMIT

Or, you can just do:
 > \set ON_ERROR_ROLLBACK on


--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University

Attachment

smime.p7s