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

On 04/09/2016 12:00 AM, durumdara@gmail.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.

I am not seeing silent:

test=> begin ;
BEGIN
test=> insert into a values (1, 23, 56, 98);
INSERT 0 1
test=> update a set v1 = 25 where id = 1;
UPDATE 1
test=> update a set v1 = 25 where id = 2;
UPDATE 0
test=> update a set v0 = 25 where id = 2;
ERROR:  column "v0" of relation "a" does not exist
LINE 1: update a set v0 = 25 where id = 2;
                      ^
test=> update a set v1 = 25 where id = 2;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

test=> rollback ;
ROLLBACK

or if try the commit:

test=> commit;
ROLLBACK


> My client controls, my client libraries, my client users believe that
> changes were sent.

What are your client and client libraries?

>
> My client library lies that I'm "InTransaction", and in same transaction
> I started(?). Every statement creates error message.

You are in the same transaction block until you issue the ROLLBACK or
COMMIT.

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

I do not understand the above.

>
> 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, seeSET XACT_ABORT (Transact-SQL)
>     <https://technet.microsoft.com/en-us/library/ms188792%28v=sql.105%29.aspx>.
>
>
> Thanks for your help!
>
> dd


--
Adrian Klaver
adrian.klaver@aklaver.com