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