From: | Alexander Pyhalov <alp(at)rsu(dot)ru> |
---|---|
To: | "Vogt, Michael" <Michael(dot)Vogt(at)united-security-providers(dot)ch> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Autocommit off - commits/rollbacks |
Date: | 2011-03-14 16:27:46 |
Message-ID: | 4D7E4202.5000108@rsu.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello.
On 03/14/2011 12:24, Vogt, Michael wrote:
> 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:
....
AFAIK, sqlplus uses savepoints behind the scene. So, you can do
something like this:
> create table t1 (i int unique);
CREATE TABLE
> begin;
BEGIN
*> insert into t1 values(1);
INSERT 0 1
*> savepoint s1;
SAVEPOINT
*> insert into t1 values(2);
INSERT 0 1
*> savepoint s2;
SAVEPOINT
*> insert into t1 values(2);
ERROR: duplicate key value violates unique constraint "t1_i_key"
!> ROLLBACK TO SAVEPOINT s2;
ROLLBACK
*> commit;
COMMIT
Or, you can just do:
> \set ON_ERROR_ROLLBACK on
--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2011-03-14 16:28:15 | Re: Primary key |
Previous Message | Andy Colson | 2011-03-14 16:24:27 | Re: Select for update with offset interferes with concurrent transactions |