Search Postgresql Archives

Autocommit off - commits/rollbacks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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>>


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux