Autocommit off - commits/rollbacks

From: "Vogt, Michael" <Michael(dot)Vogt(at)united-security-providers(dot)ch>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Autocommit off - commits/rollbacks
Date: 2011-03-14 14:55:37
Message-ID: 98D8B015D09D1241A15FD54BA7EAFF150253C2E5@ttsrv02.tetrade.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tv 2011-03-14 15:04:42 Re: Postgres 8.3 vs. 8.4 - Query plans and performance
Previous Message Alexander Pyhalov 2011-03-14 14:54:26 Re: DBMS upgrade and backups