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