Most recent driver aborts transaction after one error

This is a multi-part message in MIME format.
--------------090404010907080106020606
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:


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

--------------090404010907080106020606
Content-Type: text/plain;
name="mylog_1332.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="mylog_1332.log"

[3348]globals.extra_systable_prefixes = 'dd_;'
[3348]aszKey='DSN', value='postgres'
[3348]copyAttributes: DSN='postgres',server='',dbase='',user='',passwd='xxxxx',por t='',sslmode='',onlyread='',conn_settings='',disallow_premat ure=-1)
[3348]globals.extra_systable_prefixes = 'dd_;'
[3348]globals.extra_systable_prefixes = 'dd_;'


--------------090404010907080106020606
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--------------090404010907080106020606--

Re: Most recent driver aborts transaction after one error

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:
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Most recent driver aborts transaction after one error

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:
>>
>>
>> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Most recent driver aborts transaction after one error

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Most recent driver aborts transaction after one error

This is a multi-part message in MIME format.
--------------020900030506090006010809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------020900030506090006010809
Content-Type: text/plain;
name="mylog_2636_continueaftercrash.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="mylog_2636_continueaftercrash.log"

[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',por t='',onlyread='',protocol='',conn_settings='',disallow_prema ture=-1)
[2412]attribute = 'UID', value = 'kdba'
[2412]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx' ,port='',onlyread='',protocol='',conn_settings='',disallow_p remature=-1)
[2412]attribute = 'PWD', value = 'xxxxx'
[2412]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx' ,port='',onlyread='',protocol='',conn_settings='',disallow_p remature=-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: about to 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

--------------020900030506090006010809
Content-Type: text/plain;
name="mylog_1008_stopaftercrash.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="mylog_1008_stopaftercrash.log"

[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',por t='',onlyread='',protocol='',conn_settings='',disallow_prema ture=-1)
[3692]attribute = 'UID', value = 'kdba'
[3692]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx' ,port='',onlyread='',protocol='',conn_settings='',disallow_p remature=-1)
[3692]attribute = 'PWD', value = 'xxxxx'
[3692]copyAttributes: DSN='postgres',server='',dbase='',user='kdba',passwd='xxxxx' ,port='',onlyread='',protocol='',conn_settings='',disallow_p remature=-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: about to 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

--------------020900030506090006010809
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--------------020900030506090006010809--

Re: Most recent driver aborts transaction after one error

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Most recent driver aborts transaction after one error

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Most recent driver aborts transaction after one error

> 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:
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Most recent driver aborts transaction after one error

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:
>>
>>
>> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq