Обсуждение: Most recent driver aborts transaction after one error
Hi there, I have just upgraded to PostgreSQL 8.1 and I have encountered the following problem. When I connect through psqlODBC 8.01.0200 (PostgreSQL Unicode), a sequence like the following: <start a transaction> DROP SEQUENCE BAZ; SELECT 1; will give an error on the DROP SEQUENCE: "42P01: Error while executing the query; ERROR: sequence "app_bod_seq" does not exist" and will then give an error on the SELECT 1: "25P02: Error while executing the query; ERROR: current transaction is aborted, commands ignored until end of transaction block" When connecting through the psqlODBC 8.00.0102, I do *not* get the second error. This is, in fact, what I would expect. It is also what pretty much all other databases do (our application also runs on Informix, Firebird, Oracle and MS SQL Server, and they all allow failed commands in transactions without forcing a rollback). And it is what the 8.00.0102 driver did (or appeared to do?). Was this behaviour changed on purpose, and if so, why? And is there a way to work around it? ;-) --Bart [3348]globals.extra_systable_prefixes = 'dd_;' [3348]aszKey='DSN', value='postgres' [3348]copyAttributes: DSN='postgres',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='',onlyread='',conn_settings='',disallow_premature=-1) [3348]globals.extra_systable_prefixes = 'dd_;' [3348]globals.extra_systable_prefixes = 'dd_;'
Bart Samwel wrote: > Hi there, > > I have just upgraded to PostgreSQL 8.1 and I have encountered the > following problem. When I connect through psqlODBC 8.01.0200 (PostgreSQL > Unicode), a sequence like the following: > > <start a transaction> > DROP SEQUENCE BAZ; > SELECT 1; > > will give an error on the DROP SEQUENCE: > > "42P01: Error while executing the query; > ERROR: sequence "app_bod_seq" does not exist" > > and will then give an error on the SELECT 1: > > "25P02: Error while executing the query; > ERROR: current transaction is aborted, commands ignored until end of > transaction block" > > When connecting through the psqlODBC 8.00.0102, I do *not* get the > second error. This is, in fact, what I would expect. It is also what > pretty much all other databases do (our application also runs on > Informix, Firebird, Oracle and MS SQL Server, and they all allow failed > commands in transactions without forcing a rollback). And it is what the > 8.00.0102 driver did (or appeared to do?). Was this behaviour changed on > purpose, and if so, why? And is there a way to work around it? ;-) Please try Experimental Enhanced Branch(psqlODBC 07.03.0260) at http://pgfoundry.org/projects/psqlodbc/ . You can specify the *Level of rollback on errors* option as *Statement* using the version. In addtion you had better replace the psqlodbc35w.dll with the one at http://www.geocities.jp/inocchichichi/psqlodbc/index.html because it constains bug fixes for George Weaver's problem and (at least a part of) Daniel Holm's problem. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bart Samwel wrote: >> Hi there, >> >> I have just upgraded to PostgreSQL 8.1 and I have encountered the >> following problem. When I connect through psqlODBC 8.01.0200 (PostgreSQL >> Unicode), a sequence like the following: >> >> <start a transaction> >> DROP SEQUENCE BAZ; >> SELECT 1; >> >> will give an error on the DROP SEQUENCE: >> >> "42P01: Error while executing the query; >> ERROR: sequence "app_bod_seq" does not exist" >> >> and will then give an error on the SELECT 1: >> >> "25P02: Error while executing the query; >> ERROR: current transaction is aborted, commands ignored until end of >> transaction block" >> >> When connecting through the psqlODBC 8.00.0102, I do *not* get the >> second error. This is, in fact, what I would expect. It is also what >> pretty much all other databases do (our application also runs on >> Informix, Firebird, Oracle and MS SQL Server, and they all allow failed >> commands in transactions without forcing a rollback). And it is what the >> 8.00.0102 driver did (or appeared to do?). Was this behaviour changed >> on purpose, and if so, why? And is there a way to work around it? ;-) > > Please try Experimental Enhanced Branch(psqlODBC 07.03.0260) at > http://pgfoundry.org/projects/psqlodbc/ . > You can specify the *Level of rollback on errors* option as *Statement* > using the version. After working around some problems, my test situation worked perfectly. Thanks for the hint! These were the problems I encountered: 1. SQLGetInfo(SQL_DBMS_NAME) returns "PostgreSQL35W", and SQLGetInfo(SQL_DBMS_VER) returns a number in the 07.30 range, while I'm running PostgreSQL 8.1. I think that "PostgreSQL35W" is fine for a SQL_DRIVER_NAME, but SQL_DBMS_NAME should be simply "PostgreSQL", and SQL_DBMS_VER should return the DBMS version, not the driver version (which should be returned by SQL_DRIVER_VER). 2. I also encountered an access violation while exiting. The call stack was not very informative: ntdll.dll!_RtlpWaitForCriticalSection@4() + 0x5b ntdll.dll!_RtlEnterCriticalSection@4() + 0x46 psqlodbc35w.dll!023a097e() ntdll.dll!_RtlpFreeDebugInfo@4() + 0x57 ntdll.dll!_RtlDeleteCriticalSection@4() + 0x78 If you want me to try this with a debug-compiled psqlodbc35w, just say the word! Cheers, Bart
Bart Samwel wrote: > Hiroshi Inoue wrote: > >> Bart Samwel wrote: >> > After working around some problems, my test situation worked > perfectly. Thanks for the hint! > > These were the problems I encountered: > > 1. SQLGetInfo(SQL_DBMS_NAME) returns "PostgreSQL35W", and > SQLGetInfo(SQL_DBMS_VER) returns a number in the 07.30 range, while > I'm running PostgreSQL 8.1. I think that "PostgreSQL35W" is fine for a > SQL_DRIVER_NAME, but SQL_DBMS_NAME should be simply "PostgreSQL", and > SQL_DBMS_VER should return the DBMS version, not the driver version > (which should be returned by SQL_DRIVER_VER). I will examine it. Thanks. > > 2. I also encountered an access violation while exiting. The call > stack was not very informative: > > ntdll.dll!_RtlpWaitForCriticalSection@4() + 0x5b > ntdll.dll!_RtlEnterCriticalSection@4() + 0x46 > psqlodbc35w.dll!023a097e() > ntdll.dll!_RtlpFreeDebugInfo@4() + 0x57 > ntdll.dll!_RtlDeleteCriticalSection@4() + 0x78 > > If you want me to try this with a debug-compiled psqlodbc35w, just say > the word! It looks like a thread is about to Free a Handle while another thread is wating for the Handle. OK please send me the mylog output. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bart Samwel wrote: >> >> 2. I also encountered an access violation while exiting. The call >> stack was not very informative: >> >> ntdll.dll!_RtlpWaitForCriticalSection@4() + 0x5b >> ntdll.dll!_RtlEnterCriticalSection@4() + 0x46 >> psqlodbc35w.dll!023a097e() ntdll.dll!_RtlpFreeDebugInfo@4() + >> 0x57 ntdll.dll!_RtlDeleteCriticalSection@4() + 0x78 >> If you want me to try this with a debug-compiled psqlodbc35w, just say >> the word! > > > It looks like a thread is about to Free a Handle while another thread is > wating for the Handle. > OK please send me the mylog output. I've attached two mylogs: in the first, I ordered my debugger to continue after the access violation, in the second, I immediately killed the program at the point of the access violation. If you need more info, let me know. Cheers, Bart [2412]globals.extra_systable_prefixes = 'dd_;' [2412][[SQLAllocHandle]][2412]**** in PGAPI_AllocEnv ** [2412]** exit PGAPI_AllocEnv: phenv = 23f4dc8 ** [2412][[SQLSetEnvAttr]] att=200,2 [2412][[SQLAllocHandle]][2412]PGAPI_AllocConnect: entering... [2412]**** PGAPI_AllocConnect: henv = 23f4dc8, conn = 23f4e08 [2412]EN_add_connection: self = 23f4dc8, conn = 23f4e08 [2412] added at i =0, conn->henv = 23f4dc8, conns[i]->henv = 23f4dc8 [2412][SQLGetInfoW(30)][2412]PGAPI_GetInfo: entering...fInfoType=77 [2412]PGAPI_GetInfo: p='03.51', len=0, value=0, cbMax=12 [2412][SQLSetConnectAttrW][2412]PGAPI_SetConnectAttr 115 1 [2412]the application is ansi [2412][SQLSetConnectAttrW][2412]PGAPI_SetConnectAttr 103 f [2412]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15 [2412][SQLDriverConnectW][2412]PGAPI_DriverConnect: entering... [2412]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [2412]our_connect_string = 'DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [2412]attribute = 'DSN', value = 'postgres' [2412]copyAttributes: DSN='postgres',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [2412]attribute = 'UID', value = 'kdba' [2412]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [2412]attribute = 'PWD', value = 'xxxxx' [2412]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [2412]getDSNinfo: DSN=postgres overwrite=0 [2412]globals.extra_systable_prefixes = 'dd_;' [2412]rollback_on_error=2 [2412]globals.extra_systable_prefixes = 'dd_;' [2412]our_connect_string = 'DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [2412]attribute = 'DSN', value = 'postgres' [2412]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2412]attribute= 'UID', value= 'kdba' [2412]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2412]attribute= 'PWD', value= 'xxxxx' [2412]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2412]PGAPI_Disconnect: aboutto CC_cleanup [2412]in CC_Cleanup, self=23f4e08 [2412]after CC_abort [2412]SOCK_Destructor [2412]after SOCK destructor [2412]exit CC_Cleanup [2412]PGAPI_Disconnect: done CC_cleanup [2412]PGAPI_Disconnect: returning... [2412][[SQLFreeHandle]][2412]PGAPI_FreeConnect: entering... [2412]**** in PGAPI_FreeConnect: hdbc=23f4e08 [2412]enter CC_Destructor, self=23f4e08 [2412]in CC_Cleanup, self=23f4e08 [2412]after SOCK destructor [2412]exit CC_Cleanup [2412]after CC_Cleanup [2412]after free statement holders [2412]exit CC_Destructor [2412]PGAPI_FreeConnect: returning... [2412][[SQLFreeHandle]][2412]**** in PGAPI_FreeEnv: env = 23f4dc8 ** [2412]in EN_Destructor, self=23f4dc8 [2412]exit EN_Destructor: rv = 1 [2412] ok [3692]globals.extra_systable_prefixes = 'dd_;' [3692][[SQLAllocHandle]][3692]**** in PGAPI_AllocEnv ** [3692]** exit PGAPI_AllocEnv: phenv = 23f4dc8 ** [3692][[SQLSetEnvAttr]] att=200,2 [3692][[SQLAllocHandle]][3692]PGAPI_AllocConnect: entering... [3692]**** PGAPI_AllocConnect: henv = 23f4dc8, conn = 23f4e08 [3692]EN_add_connection: self = 23f4dc8, conn = 23f4e08 [3692] added at i =0, conn->henv = 23f4dc8, conns[i]->henv = 23f4dc8 [3692][SQLGetInfoW(30)][3692]PGAPI_GetInfo: entering...fInfoType=77 [3692]PGAPI_GetInfo: p='03.51', len=0, value=0, cbMax=12 [3692][SQLSetConnectAttrW][3692]PGAPI_SetConnectAttr 115 1 [3692]the application is ansi [3692][SQLSetConnectAttrW][3692]PGAPI_SetConnectAttr 103 f [3692]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15 [3692][SQLDriverConnectW][3692]PGAPI_DriverConnect: entering... [3692]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [3692]our_connect_string = 'DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [3692]attribute = 'DSN', value = 'postgres' [3692]copyAttributes: DSN='postgres',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [3692]attribute = 'UID', value = 'kdba' [3692]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [3692]attribute = 'PWD', value = 'xxxxx' [3692]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1) [3692]getDSNinfo: DSN=postgres overwrite=0 [3692]globals.extra_systable_prefixes = 'dd_;' [3692]rollback_on_error=2 [3692]globals.extra_systable_prefixes = 'dd_;' [3692]our_connect_string = 'DSN=postgres;UID=kdba;PWD=xxxxxxxx;' [3692]attribute = 'DSN', value = 'postgres' [3692]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3692]attribute= 'UID', value= 'kdba' [3692]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3692]attribute= 'PWD', value= 'xxxxx' [3692]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3692]PGAPI_Disconnect: aboutto CC_cleanup [3692]in CC_Cleanup, self=23f4e08 [3692]after CC_abort [3692]SOCK_Destructor [3692]after SOCK destructor [3692]exit CC_Cleanup [3692]PGAPI_Disconnect: done CC_cleanup [3692]PGAPI_Disconnect: returning... [3692][[SQLFreeHandle]][3692]PGAPI_FreeConnect: entering... [3692]**** in PGAPI_FreeConnect: hdbc=23f4e08 [3692]enter CC_Destructor, self=23f4e08 [3692]in CC_Cleanup, self=23f4e08 [3692]after SOCK destructor [3692]exit CC_Cleanup [3692]after CC_Cleanup [3692]after free statement holders [3692]exit CC_Destructor [3692]PGAPI_FreeConnect: returning... [3692][[SQLFreeHandle]][3692]**** in PGAPI_FreeEnv: env = 23f4dc8 ** [3692]in EN_Destructor, self=23f4dc8 [3692]exit EN_Destructor: rv = 1 [3692] ok
Bart Samwel wrote: > Hiroshi Inoue wrote: > >> Bart Samwel wrote: >> >>> >>> 2. I also encountered an access violation while exiting. The call >>> stack was not very informative: >>> >>> ntdll.dll!_RtlpWaitForCriticalSection@4() + 0x5b >>> ntdll.dll!_RtlEnterCriticalSection@4() + 0x46 >>> psqlodbc35w.dll!023a097e() ntdll.dll!_RtlpFreeDebugInfo@4() >>> + 0x57 ntdll.dll!_RtlDeleteCriticalSection@4() + 0x78 If >>> you want me to try this with a debug-compiled psqlodbc35w, just say >>> the word! >> >> >> >> It looks like a thread is about to Free a Handle while another thread >> is wating for the Handle. >> OK please send me the mylog output. > > > I've attached two mylogs: in the first, I ordered my debugger to > continue after the access violation, in the second, I immediately > killed the program at the point of the access violation. If you need > more info, let me know. > Thanks. Please retry the dll at http://www.geocities.jp/inocchichichi/psqlodbc/index.html . Check SQLGetInfo with the option SQL_DBMS_NAME or SQL_DBMS_VERSION as well. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bart Samwel wrote: > >> Hiroshi Inoue wrote: >> >>> Bart Samwel wrote: >>> >>>> >>>> 2. I also encountered an access violation while exiting. The call >>>> stack was not very informative: >>>> >>>> ntdll.dll!_RtlpWaitForCriticalSection@4() + 0x5b >>>> ntdll.dll!_RtlEnterCriticalSection@4() + 0x46 >>>> psqlodbc35w.dll!023a097e() ntdll.dll!_RtlpFreeDebugInfo@4() >>>> + 0x57 ntdll.dll!_RtlDeleteCriticalSection@4() + 0x78 If >>>> you want me to try this with a debug-compiled psqlodbc35w, just say >>>> the word! >>> >>> >>> >>> It looks like a thread is about to Free a Handle while another thread >>> is wating for the Handle. >>> OK please send me the mylog output. >> >> >> I've attached two mylogs: in the first, I ordered my debugger to >> continue after the access violation, in the second, I immediately >> killed the program at the point of the access violation. If you need >> more info, let me know. >> > > Thanks. > Please retry the dll at > http://www.geocities.jp/inocchichichi/psqlodbc/index.html . > Check SQLGetInfo with the option SQL_DBMS_NAME or SQL_DBMS_VERSION > as well. OK, that fixes both the access violation and the DBMS_NAME and DBMS_VERSION issues. The DBMS_NAME is now "PostgreSQL", and the version is 8.1.3, as expected. Thanks for the quick response! --Bart
> I have just upgraded to PostgreSQL 8.1 and I have encountered the > following problem. When I connect through psqlODBC 8.01.0200 (PostgreSQL > Unicode), a sequence like the following: > > <start a transaction> > DROP SEQUENCE BAZ; > SELECT 1; > > will give an error on the DROP SEQUENCE: > > "42P01: Error while executing the query; > ERROR: sequence "app_bod_seq" does not exist" > > and will then give an error on the SELECT 1: > > "25P02: Error while executing the query; > ERROR: current transaction is aborted, commands ignored until end of > transaction block" > > When connecting through the psqlODBC 8.00.0102, I do *not* get the > second error. This is, in fact, what I would expect. It is also what > pretty much all other databases do (our application also runs on > Informix, Firebird, Oracle and MS SQL Server, and they all allow failed > commands in transactions without forcing a rollback). And it is what the > 8.00.0102 driver did (or appeared to do?). Was this behaviour changed on > purpose, and if so, why? And is there a way to work around it? ;-) This is backend related. I assume you use autocommit=off. In this case backend doesn't allow next commands after failed one in one transaction. 08.00.0102 driver calls automatic rollback (if I remember it right). Some users voted againist it. And I agreed with them. When programmer manage the transaction he may also manage errors. I see no reason to continue transaction when something in it failed. Transaction may be atomic. All inside is ok or no change happens. It sounds quite odd for me that another RDBMS do it another way. Please could you explain it to me better? What behaviour do you expect when fail second SQL command, ... Regards, Luf
Ludek Finstrle wrote: >> I have just upgraded to PostgreSQL 8.1 and I have encountered the >> following problem. When I connect through psqlODBC 8.01.0200 (PostgreSQL >> Unicode), a sequence like the following: >> >> <start a transaction> >> DROP SEQUENCE BAZ; >> SELECT 1; >> >> will give an error on the DROP SEQUENCE: >> >> "42P01: Error while executing the query; >> ERROR: sequence "app_bod_seq" does not exist" >> >> and will then give an error on the SELECT 1: >> >> "25P02: Error while executing the query; >> ERROR: current transaction is aborted, commands ignored until end of >> transaction block" >> >> When connecting through the psqlODBC 8.00.0102, I do *not* get the >> second error. This is, in fact, what I would expect. It is also what >> pretty much all other databases do (our application also runs on >> Informix, Firebird, Oracle and MS SQL Server, and they all allow failed >> commands in transactions without forcing a rollback). And it is what the >> 8.00.0102 driver did (or appeared to do?). Was this behaviour changed on >> purpose, and if so, why? And is there a way to work around it? ;-) > > This is backend related. I assume you use autocommit=off. In this > case backend doesn't allow next commands after failed one in > one transaction. I get what autocommit does when I haven't started a transaction, but what does "autocommit" mean when I'm inside a manually started transaction? > 08.00.0102 driver calls automatic rollback (if I remember it right). > Some users voted againist it. And I agreed with them. When programmer > manage the transaction he may also manage errors. Yes. But that also means that he needs to get a choice on whether to continue or not. The 8.01 driver forces the programmer to rollback, which is not very nice. > I see no reason to continue transaction when something in it failed. > Transaction may be atomic. All inside is ok or no change happens. > It sounds quite odd for me that another RDBMS do it another way. Well, on other DBMSes the statements themselves seem to act like small transactions themselves. If a single statement fails, you can try and finish your transaction in another way, or you can roll it back. Your choice. > Please could you explain it to me better? What behaviour do you expect > when fail second SQL command, ... In my case, it's not a problem if the "DROP SEQUENCE" statement fails, because I'm only dropping it in order to be able to recreate it. Other databases just act as if the statement never happened, i.e., the transaction is in the same state as before the failed command started. That's what I expect. Cheers, Bart