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

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

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(dot)klaver(at)aklaver(dot)com