Обсуждение: 9.5 - Is there any way to disable automatic rollback?
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).
Thanks for your help!
dd
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).
Thanks for your help!
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
transaction.
I am not seeing silent:
ERROR: current transaction is aborted, commands ignored until end of transaction block
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.
Hello,
if you are using pgjdbc, there is a discussion about adding an option to modify this behavior:
https://github.com/pgjdbc/pgjdbc/issues/423
This would simplify the migration of java projects, e.g. from oracle to postgres.
regards,
Marc Mamin
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Durumdara
Sent: Montag, 11. April 2016 16:02
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.5 - Is there any way to disable automatic rollback?
Dear All!
>In PG it's seems to be different. PG silently rollback the actual
transaction.
I am not seeing silent:
ERROR: current transaction is aborted, commands ignored until end of transaction block
I say "silently", because first I didn't recognize that all things lost, not only last stmt.
And I also say it, because the client library shows this error, but datasets are remaining in edited, modified state (not just last record, all priorly edited tables)
My client controls, my client libraries, my client users believe that
changes were sent.
What are your client and client libraries?
PGDAC.
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.
Yes, I see. For Commit or Rollback I don't execute any selects, for example: "select txid_current()", because I got that error...
Thanks.
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.
In MS or FireBird the statements' modifications were stored in DB in limbo state, so I can commit the prior statements. For this there are only savepoints I think.
Thanks for your answers!
dd