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