Re: 9.5 - Is there any way to disable automatic rollback?

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "durumdara(at)gmail(dot)com" <durumdara(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: 9.5 - Is there any way to disable automatic rollback?
Date: 2016-04-09 07:29:34
Message-ID: CAKFQuwayPPeRXENbyG=+nHEoSXeGASBDa37BJDKGGyywOdRe+Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Saturday, April 9, 2016, durumdara(at)gmail(dot)com <durumdara(at)gmail(dot)com> wrote:

> Dear Everybody!
>
>
> See this sampe:
>
> StartTrans;
> try
> Update1;
> Insert1;
> Update2; // this cause error f.e.
> Commit;
> except
> AnyChecks;
> Rollback;
>
> When Update2 causes error, AnyChecks comes.
>
> In other databases I can do anything in that point, because Update and
> Insert 1 stored in the database, and the transaction is on.
> May I choose to commit. The control is mine.
>
> In PG it's seems to be different. PG silently rollback the actual
> transaction.
> My client controls, my client libraries, my client users believe that
> changes were sent.
>
> My client library lies that I'm "InTransaction", and in same transaction I
> started(?). Every statement creates error message.
> I think it's a little bit problematic. This is not under my control.
> In AutoCommit mode ok, because it must drop the last modification, but
> here no, I think.
>
> Please help me a little: have I got any way to disable this mode, or turn
> it on/off?
>
> MS:
>
> If a run-time statement error (such as a constraint violation) occurs in a
> batch, the default behavior in the Database Engine is to roll back only the
> statement that generated the error. You can change this behavior using the
> SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time
> statement error causes an automatic rollback of the current transaction.
> Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
> For more information, see SET XACT_ABORT (Transact-SQL)
> <https://technet.microsoft.com/en-us/library/ms188792%28v=sql.105%29.aspx>
> .
>
>
> Thanks for your help!
>
>

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

Error trapping section.

Also, SAVEPOINT can factor into this.

But, as written, you cannot. PostgreSQL cannot be made to change its
default transaction behavior to conform to MS's default. At least not that
I see documented or recall from previous times this question has been asked.

David J.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Durumdara 2016-04-09 08:30:49 Really unique session ID - PID + connection timestamp?
Previous Message durumdara@gmail.com 2016-04-09 07:00:38 9.5 - Is there any way to disable automatic rollback?