[DB-SIG] autocommit support in pep-249
Chris.Clark at ingres.com
Tue Sep 13 19:53:16 CEST 2011
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:
> At this point we have an open transaction with two inserts pending. We
> are then going to:
> 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:
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
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
More information about the DB-SIG