autocommit support in pep-249

classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

Joshua D. Drake

On 09/14/2011 10:25 AM, Michael Bayer wrote:
>
>
> On Sep 14, 2011, at 12:56 PM, Joshua D. Drake wrote:
>
>> No. What I am saying is, the begin() is optional. If you look at my example, if PostgreSQL is in autocommit mode, the only way to start a transaction is to execute a query to start the transaction (BEGIN or START TRANSACTION). From a, "it would be nice if" perspective, I would like to have the option of using .begin() to start a transaction. That seems to jive better with proper coding.
>>
>
> "proper" on postgresql sure.  On Oracle, there is no BEGIN.   I like that the DBAPI tries to simplify this disconnect with a consistent, moderately coarse-grained facade rather than confusing users and burdening DBAPI authors with two ways to do it.
>

Thus the "optional". Those that can make use of it can, those that don't
don't need it or it will be silently ignored.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

M.-A. Lemburg
In reply to this post by Chris Clark-2
Chris Clark wrote:

> M.-A. Lemburg wrote:
>> Chris Clark wrote:
>>  
>>> Hi All,
>>>
>>> I was discussing with someone today autocommit support and found an area
>>> of pep-249 where I think we could improve the wording (and the spec):
>>>
>>> http://www.python.org/dev/peps/pep-0249/
>>>
>>> .....
>>>
>>>        .commit()
>>>                     Commit any pending transaction to the database. Note
>>> that
>>>            if the database supports an auto-commit feature, this must
>>>            be initially off. An interface method may be provided to
>>>            turn it back on.
>>>
>>> .....
>>>
>>> It is the last sentence that I'm looking at:
>>>
>>>    
>>>> An interface method may be provided to turn it back on.
>>>>      
>>> Comments:
>>>
>>>    * The "may" clearly marks this as an optional interface (and I'm
>>>      happy about that).
>>>    * It would be helpful to make clear what the interface method would
>>>      be so module authors can implement this consistently
>>>    * if autocommit can be turned on in a connection, there should have
>>>      a way to turn it off again
>>> ...
>>> Comments?
>>>    
>>
>> mxODBC and mxODBC Connect will support a writable connection
>> attribute connection.autocommit to turn the feature on/off.
>> This works by assigning a boolean to the attribute. The
>> attribute also allows for an easy way to check whether autocommit
>> is active or not.
>>
>> They both already support doing this via the (non-standard) DB-API
>> extension connection.setconnectoption() method.
>>
>> From an API perspective, using an attribute is the right
>> and user-friendly approach. My only gripe with this is the fact,
>> that querying or setting the attribute can cause exceptions,
>> which you'd normally only expect from function/method calls.
>>
>> Other than that I'm +1 on adding the attribute as standard
>> DB-API 2.0 extension.
>>
>>  
>
> Thanks M.A..
>
> It looks like we are heading towards a consensus on an exception being
> used (when mid transaction and autocommit on is requested). So we should
> document that, I'm inclined to be a little whishy-washy and have a short
> line along the lines of:
>
>    If an attempt is made to enable autocommit mid transactions this is
> likely to cause a DBI exception, the exception raised may vary between
> drivers/backends but will be a sub-class of driver.Error.

I actually like the way ODBC handles these cases:

* Turning on autocommit has the same effect as doing a .commit()
and then proceeding in autocommit mode.

* Turning off autocommit causes a new transaction to start.

but I guess we cannot require this from database modules, since
the semantics are usually defined by the database backend and not
the client lib or driver.

> I'd like to try and cover the case mentioned above, where "*querying* or
> setting the attribute can cause exceptions". Is this likely? If so we
> should document that with a one liner too.

It's not likely, but can happen, e.g. if the connection to the
database is lost or the database doesn't support transactions or
if the backend doesn't support transactions (turning autocommit
off would then raise an exception).

> I'd like to steal your wording for documentation, I've only changed the
> first few words:
>
> ---------
> A connection may optionally support a writable connection
> attribute connection.autocommit to turn the feature on/off.
> This works by assigning a boolean to the attribute. The
> attribute also allows for an easy way to check whether autocommit
> is active or not.
>
> If an attempt is made to enable autocommit mid transactions this is
> likely to cause a DBI exception, the exception raised may vary between
> drivers/backends but will be a sub-class of driver.Error. A driver may
> choose to implement autocommit manually (that is, manually issue commits
> at the end of each cursor operation), to raise an exception if mid
> transaction, or pass the autocommit request to the backend and raise an
> exception for errors that the backend raises.
> ---------

I'm not sure about that last paragraph. The exception raising
part should really just be about turning autocommit on/off, not about
the way exceptions are raised as a result of the autocommit
implementation during cursor operations.

> It may be that we have more discussion on the commit/rollback options
> but I'm not planning on raising those options again unless there is
> interest from other posters.
>
> RE the conn.begin() that is a -1 from me, this seems like a separate
> item to autocommit and may deserve a new thread. This doesn't prevent
> implementers from adding a non-standard exception though if it will
> prove to be useful for some users.

Agreed.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 14 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-10-04: PyCon DE 2011, Leipzig, Germany                20 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

Chris Clark-2
M.-A. Lemburg wrote:

> Chris Clark wrote:
>  
>> M.-A. Lemburg wrote:
>>    
>>> Chris Clark wrote:
>>>  
>>>      
>>>> Hi All,
>>>>
>>>> I was discussing with someone today autocommit support and found an area
>>>> of pep-249 where I think we could improve the wording (and the spec):
>>>>
>>>> http://www.python.org/dev/peps/pep-0249/
>>>>
>>>> .....
>>>>
>>>>        .commit()
>>>>                     Commit any pending transaction to the database. Note
>>>> that
>>>>            if the database supports an auto-commit feature, this must
>>>>            be initially off. An interface method may be provided to
>>>>            turn it back on.
>>>>
>>>> .....
>>>>
>>>> It is the last sentence that I'm looking at:
>>>>
>>>>    
>>>>        
>>>>> An interface method may be provided to turn it back on.
>>>>>      
>>>>>          
>>>> Comments:
>>>>
>>>>    * The "may" clearly marks this as an optional interface (and I'm
>>>>      happy about that).
>>>>    * It would be helpful to make clear what the interface method would
>>>>      be so module authors can implement this consistently
>>>>    * if autocommit can be turned on in a connection, there should have
>>>>      a way to turn it off again
>>>> ...
>>>> Comments?
>>>>    
>>>>        
>>> mxODBC and mxODBC Connect will support a writable connection
>>> attribute connection.autocommit to turn the feature on/off.
>>> This works by assigning a boolean to the attribute. The
>>> attribute also allows for an easy way to check whether autocommit
>>> is active or not.
>>>
>>> They both already support doing this via the (non-standard) DB-API
>>> extension connection.setconnectoption() method.
>>>
>>> From an API perspective, using an attribute is the right
>>> and user-friendly approach. My only gripe with this is the fact,
>>> that querying or setting the attribute can cause exceptions,
>>> which you'd normally only expect from function/method calls.
>>>
>>> Other than that I'm +1 on adding the attribute as standard
>>> DB-API 2.0 extension.
>>>
>>>  
>>>      
>> Thanks M.A..
>>
>> It looks like we are heading towards a consensus on an exception being
>> used (when mid transaction and autocommit on is requested). So we should
>> document that, I'm inclined to be a little whishy-washy and have a short
>> line along the lines of:
>>
>>    If an attempt is made to enable autocommit mid transactions this is
>> likely to cause a DBI exception, the exception raised may vary between
>> drivers/backends but will be a sub-class of driver.Error.
>>    
>
> I actually like the way ODBC handles these cases:
>
> * Turning on autocommit has the same effect as doing a .commit()
> and then proceeding in autocommit mode.
>
> * Turning off autocommit causes a new transaction to start.
>
> but I guess we cannot require this from database modules, since
> the semantics are usually defined by the database backend and not
> the client lib or driver.
>  

Aghhhh! ;-) That's what I initial sent out in my first proposal.

I think the "switching on autocommit, causes a commit" behavior is easy
to document and easy to implement. The driver could easily issue the
commit under the covers when autocommit is requested. I.e. it seems
reasonable for the driver to over ride the backend behavior to provide a
consistent interface across all backends. I'm not clear if the exception
approach can be implemented consistently. DBI drivers already have to
provide a consistent approach to transactions by not needing a "begin
transaction" call for users.

>> I'd like to try and cover the case mentioned above, where "*querying* or
>> setting the attribute can cause exceptions". Is this likely? If so we
>> should document that with a one liner too.
>>    
>
> It's not likely, but can happen, e.g. if the connection to the
> database is lost or the database doesn't support transactions or
> if the backend doesn't support transactions (turning autocommit
> off would then raise an exception).
>  

That makes sense, I have to confess I'd been envisioning the driver
keeping track of the auto commit state and assuming that if the user
sent a cursor.execute('set auto commit on') it was reasonble for the
driver to not know about it. Obviously querying the layer below would
avoid that.

Thanks for explaining that, I'm glad you did but I've changed my mind
about documenting it :-)



>> I'd like to steal your wording for documentation, I've only changed the
>> first few words:
>>
>> ---------
>> A connection may optionally support a writable connection
>> attribute connection.autocommit to turn the feature on/off.
>> This works by assigning a boolean to the attribute. The
>> attribute also allows for an easy way to check whether autocommit
>> is active or not.
>>
>> If an attempt is made to enable autocommit mid transactions this is
>> likely to cause a DBI exception, the exception raised may vary between
>> drivers/backends but will be a sub-class of driver.Error. A driver may
>> choose to implement autocommit manually (that is, manually issue commits
>> at the end of each cursor operation), to raise an exception if mid
>> transaction, or pass the autocommit request to the backend and raise an
>> exception for errors that the backend raises.
>> ---------
>>    
>
> I'm not sure about that last paragraph. The exception raising
> part should really just be about turning autocommit on/off, not about
> the way exceptions are raised as a result of the autocommit
> implementation during cursor operations.
>  

I not fond of it either :-( possibly for different reasons. The behavior
I described above is not clearly documented, I'm not sure if all
backends can/will raise an error. This is partially why I suggested
implicit commits as this is much easier for a user to grok.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

M.-A. Lemburg
In reply to this post by Joshua D. Drake
Joshua D. Drake wrote:

>
> On 09/14/2011 10:25 AM, Michael Bayer wrote:
>>
>>
>> On Sep 14, 2011, at 12:56 PM, Joshua D. Drake wrote:
>>
>>> No. What I am saying is, the begin() is optional. If you look at my
>>> example, if PostgreSQL is in autocommit mode, the only way to start a
>>> transaction is to execute a query to start the transaction (BEGIN or
>>> START TRANSACTION). From a, "it would be nice if" perspective, I
>>> would like to have the option of using .begin() to start a
>>> transaction. That seems to jive better with proper coding.
>>>
>>
>> "proper" on postgresql sure.  On Oracle, there is no BEGIN.   I like
>> that the DBAPI tries to simplify this disconnect with a consistent,
>> moderately coarse-grained facade rather than confusing users and
>> burdening DBAPI authors with two ways to do it.
>>
>
> Thus the "optional". Those that can make use of it can, those that don't
> don't need it or it will be silently ignored.

I find the following is more readable (explicit better then
implicit etc.):

    connection = connect(dbname, uid, pwd)

    # Regular user code:
    ... transactional code ...

    # Experts only section:
    try:
        connection.autocommit = True
        ... non-transactional code ...
    finally:
        connection.autocommit = False

    # Regular user code:
    ... transactional code ...

    connection.commit()

You usually only need autocommit code in case you are dealing
with databases that cannot handle database definition SQL (DDL)
inside transactions (e.g. create/drop databases or tables),
if you have a need for atomic operations, or need to
avoid locking for some special reason.

In practice, it's often better to open a separate connection
in autocommit mode, than to switch modes on a single connection.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 14 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-10-04: PyCon DE 2011, Leipzig, Germany                20 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

Vernon D. Cole
In reply to this post by Federico Di Gregorio-6
(I missed replying to the group. --- buggy mental code path ...;)

---------- Forwarded message ----------
From: Vernon Cole <[hidden email]>
Date: Thu, Sep 15, 2011 at 4:23 AM
Subject: Re: [DB-SIG] autocommit support in pep-249
To: Federico Di Gregorio <[hidden email]>




On Wed, Sep 14, 2011 at 6:16 AM, Federico Di Gregorio <[hidden email]> wrote:
[snap]

Client code can set .autocommit (yes, I am +1 for the attribute) to true
by two different code paths: the "correct" one and the "buggy" one. The
correct code path is not a problem: as long as the programmer knows the
behaviour she can do the right thing:

 1. exception => make sure to commit() or rollback() before,
                 depending on the wanted outcome;
 2. commit    => rollback() if you don't want to commit changes,
                 do nothing in the other case;
 3. rollback  => as (2), reversed.

What I am interested in is the behaviour of the driver when the code
sets .autocommit to true following a buggy code path, i.e., when there
is a pending transaction and the code is unaware of it:

 1. exception => pending transaction is lost, user (and eventually
                 also the programmer) gets a stack trace: this is
                 good because the user is sure about the outcome (data
                 is lost) _and_ has information about the problem;
 2. commit    => pending transaction is commited but it is what the
                 code was really supposed to do? noboby will know until
                 someone peeks at the database and _then_ a possibly
                 difficult bug hunting session begins;
 3. rollback  => as (2), but data is lost.

Having commit or rollbacks happen because of a bug really scares me:
spurious data starts to popup into the database and finding the bugs
usually isn't a piece of cake. That's why I vote for the exception.

federico

It's those buggy code paths that we are discussing, and I am much more interested in doing the right thing on a good code path.  We're using Python here, not Pascal.  If I wanted to force all programmers to be perfect, I'ld require that all variables be declared.   What we are discussing here is what to do in a situation which a wise programmer will never get in to.  I don't want to waste a lot of time and a lot of code making sure that the results of poor programming are consistent between different database engines.  I would advocate that the results of this questionable practice should be explicitly left as "undefined", with the cautionary note that an exception MIGHT occur, and suggest that the application programmer should either commit or rollback any in-progress transactions before changing the value of autocommit. 

  That way no complicated parsing or state tracking is required.  My __setattr__ for .autocommit would simply pass the new value on to the ADO engine to do whatever it pleased with it -- and pass back any error which the engine might decide to return.

I LIKE simple -- it usually works better.
--
Vernon



_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: autocommit support in pep-249

M.-A. Lemburg
Vernon Cole wrote:

> (I missed replying to the group. --- buggy mental code path ...;)
>
> ---------- Forwarded message ----------
> From: Vernon Cole <[hidden email]>
> Date: Thu, Sep 15, 2011 at 4:23 AM
> Subject: Re: [DB-SIG] autocommit support in pep-249
> To: Federico Di Gregorio <[hidden email]>
>
>
>
>
> On Wed, Sep 14, 2011 at 6:16 AM, Federico Di Gregorio <[hidden email]> wrote:
>
>> [snap]
>>
>> Client code can set .autocommit (yes, I am +1 for the attribute) to true
>> by two different code paths: the "correct" one and the "buggy" one. The
>> correct code path is not a problem: as long as the programmer knows the
>> behaviour she can do the right thing:
>>
>>  1. exception => make sure to commit() or rollback() before,
>>                  depending on the wanted outcome;
>>  2. commit    => rollback() if you don't want to commit changes,
>>                  do nothing in the other case;
>>  3. rollback  => as (2), reversed.
>>
>> What I am interested in is the behaviour of the driver when the code
>> sets .autocommit to true following a buggy code path, i.e., when there
>> is a pending transaction and the code is unaware of it:
>>
>>  1. exception => pending transaction is lost, user (and eventually
>>                  also the programmer) gets a stack trace: this is
>>                  good because the user is sure about the outcome (data
>>                  is lost) _and_ has information about the problem;
>>  2. commit    => pending transaction is commited but it is what the
>>                  code was really supposed to do? noboby will know until
>>                  someone peeks at the database and _then_ a possibly
>>                  difficult bug hunting session begins;
>>  3. rollback  => as (2), but data is lost.
>>
>> Having commit or rollbacks happen because of a bug really scares me:
>> spurious data starts to popup into the database and finding the bugs
>> usually isn't a piece of cake. That's why I vote for the exception.
>>
>> federico
>>
>> It's those buggy code paths that we are discussing, and I am much more
> interested in doing the right thing on a good code path.  We're using Python
> here, not Pascal.  If I wanted to force all programmers to be perfect, I'ld
> require that all variables be declared.   What we are discussing here is
> what to do in a situation which a wise programmer will never get in to.  I
> don't want to waste a lot of time and a lot of code making sure that the
> results of poor programming are consistent between different database
> engines.  I would advocate that the results of this questionable practice
> should be explicitly left as "undefined", with the cautionary note that an
> exception MIGHT occur, and suggest that the application programmer should
> either commit or rollback any in-progress transactions before changing the
> value of autocommit.
>
>   That way no complicated parsing or state tracking is required.  My
> __setattr__ for .autocommit would simply pass the new value on to the ADO
> engine to do whatever it pleased with it -- and pass back any error which
> the engine might decide to return.
>
> I LIKE simple -- it usually works better.

I agree with Vernon: the DB-API cannot mandate an exception in
case of a pending transaction, since the module may very well not
be aware of such a pending transaction:

Some databases manage
transactions on the server side and it's also possible to run
COMMIT and ROLLBACK using cursor.execute() (even though that is
discouraged).

If you then tell the user that an exception will be raised in
case of a pending transaction, she will feel mistakenly secure
about doing connection.autocommit = True without an explicit
.commit() or .rollback().

I find the implicit .commit() of setting connection.autocommit = True
very intuitive. After all, you expect the connection to take care
of the .commit()s for you when doing so.

Likewise, setting the attribute to False, means that the programmer
has to take care of the .commit()s again.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 15 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-10-04: PyCon DE 2011, Leipzig, Germany                19 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
12