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
