Postgres Pro
Downloads
Home   >   mailing lists

Thread: Most recent driver aborts transaction after one error

Most recent driver aborts transaction after one error

From
Bart Samwel
Date:
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_;'

Re: Most recent driver aborts transaction after one error

From
Hiroshi Inoue
Date:
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

Re: Most recent driver aborts transaction after one error

From
Bart Samwel
Date:
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

Re: Most recent driver aborts transaction after one error

From
Hiroshi Inoue
Date:
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

Re: Most recent driver aborts transaction after one error

From
Bart Samwel
Date:
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

Re: Most recent driver aborts transaction after one error

From
Hiroshi Inoue
Date:
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

Re: Most recent driver aborts transaction after one error

From
Bart Samwel
Date:
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

Re: Most recent driver aborts transaction after one error

From
Ludek Finstrle
Date:
> 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

Re: Most recent driver aborts transaction after one error

From
Bart Samwel
Date:
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