[DB-SIG] autocommit support in pep-249

[Chris Clark]

Joshua D. Drake wrote:
>
> On 09/13/2011 09:39 AM, Chris Clark wrote:
>>
>> Vernon Cole wrote:
>>>> Properties are just the right tools for this
>>>> task, and appear to be the most used solution in popular drivers.
>
>> So I think we have 3 people each advocating slightly different options
>> for what to do when changing auto commit options :-)
>>
>> 1. exception
>> 2. commit
>> 3. rollback
>>
>
> Just to throw some PostgreSQL two cents in here. It should not be 
> legal to change transaction state once a transaction has begun. The 
> "driver" should throw an error if that is tried. It shouldn't even 
> reach the database. Consider:
>
> conn.begin()
> insert
> insert
>
> At this point we have an open transaction with two inserts pending. We 
> are then going to:
>
> conn.autocommit()?
>
> Uh, no. The driver should throw an error and the database should 
> remain waiting for next statement whether it be commit or whatever.
>

Thanks for the feedback Joshua, could you go in to more detail to 
explain your reasoning please? I think the more justifications we have 
the easier it is to make a decision.

You made an interesting comment on the autocommit request, "It shouldn't 
even reach the database". I think you are saying the "set auto commit 
on" request should not be sent to the DBMS and be handled by the driver 
when there are open transactions. If I modify your example slightly:

conn.begin()
curs.execute('issue server non transaction based directive ')  # i.e. 
not dml, not ddl
curs.execute('issue session directive ')  # for example change session 
language of error messages, Oracle NLS settings
conn.autocommit()

What should be the behavior be in this instance? No exception being 
raised makes the most sense to me as we are not in a transaction, I'm 
unclear how the driver could make this decision unless it parses and 
understands each curs.execute call.

Some drivers like Vernon's ADO driver are DBMS independent so it is not 
reasonably for them to parse query text that is passed into into execute().

A good application won't fall foul of this but we have to deal with 
applications that are not written well so that drivers all behave 
predictably if they've implemented the spec. I personally prefer an 
exception but I think the commit is least surprising. They've asked to 
autocommit so an implicit commit makes sense.

A few notes I should have added:

    * if we go the exception route, we should document that this is
      DBMS/driver defined, i.e. don't be specific as to which exception
      is raised (other than being a dbi exception)
    * in the unlikely event of a backend that does not support
      autocommit, the driver would now have the option to emulate that
      behavior


Chris