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
