Quantcast

autocommit support in pep-249

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

autocommit support in pep-249

Chris Clark-2
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


Here are some examples of the "interface method" implemented out in the
wild today

    * It looks like mxodbc handles this as a setconnectoption() method
      on the connection.
      http://www.egenix.com/products/python/mxODBCConnect/doc/mxodbc/
    * a number of other ODBC based drivers offer an option in the
      connect() constructor (only)
    * The mysql module <http://mysql-python.sourceforge.net/> implies it
      has an method on the connection object for this conn.autocommit(),
      but this does not appear to be available in the pep-249 compatible
      API <http://mysql-python.sourceforge.net/MySQLdb-1.2.2/>!


Obviously there is plenty of scope for different implementations. I
think there is value in documenting a recommendation on what this
interface should look like.

I like the API approach mxodbc has taken (it matches ODBC very well) but
it doesn't feel Pythonic :-(

To get the ball rolling, here is an initial suggestion:

    * needs to take a boolean flag as a parameter to enable and also disable
    * needs to return the auto commit state so it can be queried

pep changes, marked between ** and **:

Connection Objects

    Connection Objects should respond to the following methods:
......

        .commit()
         
            Commit any pending transaction to the database. Note that
            if the database supports an auto-commit feature, this must
            be initially off. **See the option autocommit() method to change
            autocommit behavior.**
           
            Database modules that do not support transactions should
            implement this method with void functionality.

.....
**
    Connection Objects may optional implement and respond to the
following methods:

    autocommit(on=None)
       returns True when autocommit is on, and False when autocommit is off.
       If the keyword parameter "on" is set to True, the connection will
commit any open transactions (as if connection.commit() was issued) and
all subsequent transactions will auto commit, the method returns True if
successfully in autocommit mode.
       If the keyword parameter "on" is set to False, the connection
will commit any open transactions (as if connection.commit() was issued)
and all subsequent transactions will no longer auto commit, the method
returns False if successfully out of autocommit mode..
**

The reason for the immediate commit is to try and avoid DBMS specific
behaviors (read error conditions) with different vendors when an auto
commit is requested with open transactions.

Comments?


Chris

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

Re: autocommit support in pep-249

Vernon D. Cole
This sounds like yet another item which should go into the dbapi 3.0
spec... as soon as someone has the courage to start one.
--
Vernon

On Mon, Sep 12, 2011 at 4:20 PM, Chris Clark <[hidden email]> 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
>
>
> Here are some examples of the "interface method" implemented out in the wild
> today
>
>   * It looks like mxodbc handles this as a setconnectoption() method
>     on the connection.
>     http://www.egenix.com/products/python/mxODBCConnect/doc/mxodbc/
>   * a number of other ODBC based drivers offer an option in the
>     connect() constructor (only)
>   * The mysql module <http://mysql-python.sourceforge.net/> implies it
>     has an method on the connection object for this conn.autocommit(),
>     but this does not appear to be available in the pep-249 compatible
>     API <http://mysql-python.sourceforge.net/MySQLdb-1.2.2/>!
>
>
> Obviously there is plenty of scope for different implementations. I think
> there is value in documenting a recommendation on what this interface should
> look like.
>
> I like the API approach mxodbc has taken (it matches ODBC very well) but it
> doesn't feel Pythonic :-(
>
> To get the ball rolling, here is an initial suggestion:
>
>   * needs to take a boolean flag as a parameter to enable and also disable
>   * needs to return the auto commit state so it can be queried
>
> pep changes, marked between ** and **:
>
> Connection Objects
>
>   Connection Objects should respond to the following methods:
> ......
>
>       .commit()
>                    Commit any pending transaction to the database. Note that
>           if the database supports an auto-commit feature, this must
>           be initially off. **See the option autocommit() method to change
>           autocommit behavior.**
>                      Database modules that do not support transactions
> should
>           implement this method with void functionality.
>
> .....
> **
>   Connection Objects may optional implement and respond to the following
> methods:
>
>   autocommit(on=None)
>      returns True when autocommit is on, and False when autocommit is off.
>      If the keyword parameter "on" is set to True, the connection will
> commit any open transactions (as if connection.commit() was issued) and all
> subsequent transactions will auto commit, the method returns True if
> successfully in autocommit mode.
>      If the keyword parameter "on" is set to False, the connection will
> commit any open transactions (as if connection.commit() was issued) and all
> subsequent transactions will no longer auto commit, the method returns False
> if successfully out of autocommit mode..
> **
>
> The reason for the immediate commit is to try and avoid DBMS specific
> behaviors (read error conditions) with different vendors when an auto commit
> is requested with open transactions.
>
> Comments?
>
>
> Chris
>
> _______________________________________________
> DB-SIG maillist  -  [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig
>
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Daniele Varrazzo-2
In reply to this post by Chris Clark-2
On Mon, Sep 12, 2011 at 11:20 PM, Chris Clark <[hidden email]> wrote:

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

Agreed, it would have been long due. The quick survey I did when we
introduced the session in psycopg (last May) has been:

- MySQLdb: conn.autocommit()
- cx_Oracle: conn.autocommit (attribute, not function)
- pyodbc: conn.autocommit (attribute)
- sqlite3: conn.isolation_level = None
- KInterbaseDB: not supported

(to give context, in psycopg you had to use
conn.set_isolation_level(0) before version 2.4.2, when we introduced
better ways to manipulate the session - see
<http://archives.postgresql.org/psycopg/2011-05/msg00026.php> for the
discussion. Psycopg now uses an autocommit attribute - see
<http://initd.org/psycopg/docs/connection.html#connection.autocommit>).


> To get the ball rolling, here is an initial suggestion:
>
>   * needs to take a boolean flag as a parameter to enable and also disable
>   * needs to return the auto commit state so it can be queried

[thus]

>   autocommit(on=None)
>      returns True when autocommit is on, and False when autocommit is off.
>      If the keyword parameter "on" is set to True, the connection will
> commit any open transactions (as if connection.commit() was issued) and all
> subsequent transactions will auto commit, the method returns True if
> successfully in autocommit mode.
>      If the keyword parameter "on" is set to False, the connection will
> commit any open transactions (as if connection.commit() was issued) and all
> subsequent transactions will no longer auto commit, the method returns False
> if successfully out of autocommit mode..

Because you need a getter and a setter, I feel the right solution is
to use a property. A function that behaves as a getter when called
with no parameter and as a setter if it has one is not something
widely used in pythonland. A more natural solution, but inferior,
would be using two functions (of which choosing the name is a
bikeshedding bloodbath). Properties are just the right tools for this
task, and appear to be the most used solution in popular drivers.

The behaviour of setting autocommit when a transaction is already open
is also to be seen: there are three basic option:

- have a commit
- have a rollback
- have an error

I feel the implicit commit is the most dangerous option. An implicit
rollback may be more acceptable (it's the same behaviour of closing
the connection with a transaction in progress): I proposed it for
psycopg but, in the discussion, raising an exception proved the most
popular option.

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

Re: autocommit support in pep-249

Vernon D. Cole
> Properties are just the right tools for this
> task, and appear to be the most used solution in popular drivers.
>
I think that term "attributes" is more commonly used, rather than
"properties", but I agree completely.
"conn.autocommit = True" seems pythonic, as does "if not
conn.autocommit".   Getters and Setters feel like java. The
implementing programmer has getattr and setattr for exactly that
purpose, and the calling programmer has a clean interface.

> The behaviour of setting autocommit when a transaction is already open
> is also to be seen: there are three basic option:
>
> - have a commit
> - have a rollback
> - have an error
>
> I feel the implicit commit is the most dangerous option. An implicit
> rollback may be more acceptable (it's the same behaviour of closing
> the connection with a transaction in progress): I proposed it for
> psycopg but, in the discussion, raising an exception proved the most
> popular option.
>
I don't think I know how to even determine whether there is a
transaction in progress in ADO.(*)  If there is a way, then checking
on it just so that I can raise an exception seems expensive.  Remember
that most of the time the resulting stack trace would not be seen by
the programmer who goofed up, but a completely innocent user -- and to
her it looks like the program just crashed for no reason.  The
rollback would be easy to program and user friendly.  I vote rollback.
--
Vernon

(*) Most of you are dealing with only one underlying database, so have
only that to worry about.  I have to worry about all of your databases
(with the exception of sqlite) and tons of others which neither of us
has ever heard of.  The impression that Microsoft "Active Data
Objects" applies only to Microsoft SQL server databases is very wrong.
There are ODBC drivers for almost everything and ADO providers for
most of the rest. My user may be accessing anything from a .csv text
file to an Active Directory user list. I prefer simple, thank you.
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Chris Clark-2
Vernon Cole wrote:

>> Properties are just the right tools for this
>> task, and appear to be the most used solution in popular drivers.
>>
>>    
> I think that term "attributes" is more commonly used, rather than
> "properties", but I agree completely.
> "conn.autocommit = True" seems pythonic, as does "if not
> conn.autocommit".   Getters and Setters feel like java. The
> implementing programmer has getattr and setattr for exactly that
> purpose, and the calling programmer has a clean interface.
>  

Property is the correct name for this in Python, see
http://docs.python.org/library/functions.html

I agree using a property is a good idea, very easy to document and use.
If/when I get around to it ;-) that is the route I'll take with jyjdbc.

>> The behaviour of setting autocommit when a transaction is already open
>> is also to be seen: there are three basic option:
>>
>> - have a commit
>> - have a rollback
>> - have an error
>>
>> I feel the implicit commit is the most dangerous option. An implicit
>> rollback may be more acceptable (it's the same behaviour of closing
>> the connection with a transaction in progress): I proposed it for
>> psycopg but, in the discussion, raising an exception proved the most
>> popular option.
>>
>>    
> I don't think I know how to even determine whether there is a
> transaction in progress in ADO.(*)  If there is a way, then checking
> on it just so that I can raise an exception seems expensive.  Remember
> that most of the time the resulting stack trace would not be seen by
> the programmer who goofed up, but a completely innocent user -- and to
> her it looks like the program just crashed for no reason.  The
> rollback would be easy to program and user friendly.  I vote rollback.
>  

The exception route has the potential to be the easiest for most DBMS
implementations, most DBMSs will raise an error, so the driver doesn't
need to track state and can just propagate the DBMS error. For some
backends, state tracking would be required which is why I suggested the
"easy" commit option. Rollback seems semi safe BUT when setting
autocommit to ON the default behavior of a rollback seems a little
surprising.

I'm not in love with committing when the autocommit state is changed but
for the "enable auto commit" operation it seems logical, the user is
requesting commits take place under the covers. Conversely when
disabling autocommit (after it has previously been enabled) we are
either not in a transaction (due to auto commit being on) or we could
still be fetching mid select, in which case a commit would be the same
as  a rollback. I.e. only impact on locking, any writes to the database
would have been already auto committed.

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


I'll be happy with what ever we as a group decide (so long as we
document it), I think committing will be least surprising for end users,
so whilst I'm not keen on it I feel I should champion it.

Are people happy with this rough approach (we can thrash out
exception/rollback/commit) where this is an optional extension, i.e. not
required?

Chris

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

Re: autocommit support in pep-249

Joshua D. Drake

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.

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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Chris Clark-2
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

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

Re: autocommit support in pep-249

Joshua D. Drake

On 09/13/2011 10:53 AM, Chris Clark wrote:

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

Oh now that is interesting. Hmmm... perhaps conn.autocommit() is
actually a synonym for conn.begin() except that it sets the autocommit
property? So we would have two semantics:

conn.begin() -- Starts a transaction, will not commit unless explicit
conn.commit() is called.

conn.autocommit() -- Works in autocommit mode, calling conn.commit() is
redundant, each statement is executed as it is delivered to the database.

?

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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Michael Bayer

On Sep 13, 2011, at 2:39 PM, Joshua D. Drake wrote:

>
> Oh now that is interesting. Hmmm... perhaps conn.autocommit() is actually a synonym for conn.begin() except that it sets the autocommit property? So we would have two semantics:
>
> conn.begin() -- Starts a transaction, will not commit unless explicit conn.commit() is called.
>
> conn.autocommit() -- Works in autocommit mode, calling conn.commit() is redundant, each statement is executed as it is delivered to the database.

I'm -1 on conn.begin().   I've spent years explaining to users the beauty of the DBAPI in that it has no "begin" method.   Providing for two different modes of doing the same thing, i.e. working with a standard DBAPI connection in a transaction, or forcing autocommit to True then using begin(), creates for a more confusing and controversial interface.  

Right now, pysqlite users are shielded from SQLite's aggressive file locking by the fact that pysqlite doesn't actually emit BEGIN until DML is encountered.   There is an enhancement request on pysqlite's bugtrcker to allow this to be more configurable.   In the meantime, I sometimes get email requests stating that SQLAlchemy should be emitting "BEGIN" itself to work around this issue.    While I give these users a workaround for what's essentially a missing feature in pysqlite, SQLA never emits the BEGIN.   It makes life much easier for SQLAlchemy that DBAPIs are responsible for handling transactional markers like this and I'd hate to see the interface diluted - there is sometimes functionality within "implicit begin" that may not be easy to replicate with an "explicit begin", and having both would make life more complicated for DBAPI authors as well as users.

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

Re: autocommit support in pep-249

Daniele Varrazzo-2
In reply to this post by Chris Clark-2
On Tue, Sep 13, 2011 at 6:53 PM, Chris Clark <[hidden email]> wrote:

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

I think with Postgres the situation is slightly easier, as there is no
"non transaction" statement: any statement including DML is
transactional except a couple (such as CREATE DATABASE) that would
raise an error if run inside a transaction. Because the situation is
this simple, psycopg can just run a BEGIN before the first statement
executed, whatever it is, and there is no need to parse it; it then
knows the state of the connection and an error such as switching to
autocommit while in transaction is indeed handled by the adapter
without calling the backend.

What does the Oracle adapter currently do? From my interpretation of
the dbapi there is no explicit conn.begin(), a transaction is
implicitly started at the first statement. How does the driver
implement this behaviour? Is it something it can delegate to the
backend?

Ditto for ODBC, where parsing the statements would be not impratical
but just impossible: how does the adapter currently implement the "not
autocommit" default dbapi behaviour? Is the begin handled by the
adapter or by the backend?

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

Re: autocommit support in pep-249

Michael Bayer

On Sep 13, 2011, at 6:01 PM, Daniele Varrazzo wrote:

>
> I think with Postgres the situation is slightly easier, as there is no
> "non transaction" statement: any statement including DML is
> transactional except a couple (such as CREATE DATABASE) that would
> raise an error if run inside a transaction. Because the situation is
> this simple, psycopg can just run a BEGIN before the first statement
> executed, whatever it is, and there is no need to parse it; it then
> knows the state of the connection and an error such as switching to
> autocommit while in transaction is indeed handled by the adapter
> without calling the backend.
>
> What does the Oracle adapter currently do? From my interpretation of
> the dbapi there is no explicit conn.begin(), a transaction is
> implicitly started at the first statement. How does the driver
> implement this behaviour? Is it something it can delegate to the
> backend?
>
> Ditto for ODBC, where parsing the statements would be not impratical
> but just impossible: how does the adapter currently implement the "not
> autocommit" default dbapi behaviour? Is the begin handled by the
> adapter or by the backend?

The pysqlite adapter does actually parse incoming statements, looking for DML in an attempt to delay the BEGIN until necessary, as a means of avoiding aggressive file-locking between otherwise read-only connections to a SQLite database.   But I agree in general most DBAPIs don't do anything like this.

As for Oracle, like the DBAPI itself it has no "BEGIN" statement for transactions, and doesn't natively support "autocommit" in any way - there's always a transaction in progress that starts implicitly after a previous COMMIT or ROLLBACK.  Therefore it works very nicely with DBAPI's scheme and I doubt cx_oracle needs to do anything here.

This is one reason why i really don't want DBAPI to have a begin() - not all backends have such a concept.
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Joshua D. Drake

Hello,

So, I have been a little out of the loop on DBAPI2 for a while. I just
went back and read it and would like to change my argument a bit. (For
some bizarre reason I was thinking that .begin() was part of the spec).

It seems to me that the default behavior as it is implicit, should not
be autocommit style. It should be standard transaction style. Therefore
based on the OP:

"""
.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.
"""

At least from a PostgreSQL perspective this is invalid. If you do not
issue a begin (implicitly or not), it is going to work in an autocommit
style mode and you can't turn it off. Since we (python) issue an
implicit begin when we create a connection, PostgreSQL automatically
turns off autocommit (fun huh?).

One way to handle this would be to have a connection property that
explicitly turns on autocommit. If set to TRUE, PostgreSQL would just
act in its default behavior, if set to FALSE it would implicitly create
a transaction. Even with a long lived connection you could always send a
BEGIN; explicitly and a proper transaction would start. You would just
have to remember to call .commit() in order for your data to be commited
after the BEGIN; If you called .commit() in autocommit mode you just get
a warning that says no transaction in progress.

I think we the ability to call an explicit .begin() that can be
implemented properly up to the driver level. This:

conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
cur = conn.cursor()
cur.execute("""INSERT""")
cur.execute("""SELECT * from bar""")
cur.begin()
cur.execute("""INSERT""")
cur.commit()
cur.close()

Is much better than:

conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
cur = conn.cursor()
cur.execute("""INSERT""")
cur.execute("""SELECT * from bar""")
cur.execute("""BEGIN""")
cur.execute("""INSERT""")
cur.commit()
cur.close()

Sincerely,

Joshua D. Drake

P.S. And since we are on the topic, we really need proper prepare()




--
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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Michael Bayer

On Sep 13, 2011, at 9:15 PM, Joshua D. Drake wrote:

>
> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
> cur = conn.cursor()
> cur.execute("""INSERT""")
> cur.execute("""SELECT * from bar""")
> cur.begin()
> cur.execute("""INSERT""")
> cur.commit()
> cur.close()
>
> Is much better than:
>
> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
> cur = conn.cursor()
> cur.execute("""INSERT""")
> cur.execute("""SELECT * from bar""")
> cur.execute("""BEGIN""")
> cur.execute("""INSERT""")
> cur.commit()
> cur.close()
>

what's wrong with:

conn = psycopg2.connect(....)
cur = conn.cursor()
cur.execute(" ... sql ...")
conn.commit()

?   if one wants to work with transactions, DBAPI in it's default mode provides that.     If you want all statements to be ad-hoc such that they are "committed" immediately, whether that means COMMIT after every statement as it does on Oracle or just no BEGIN emitted as it does on Postgresql, turn on "autocommit".   "autocommit" is a flag you should only be able to change when no transactional state has otherwise accumulated, but otherwise, switch it any time.  

Adding explicit begin() means the DBAPI starts to look confused as to how it should be used - DBAPI authors also have to support two different transactional styles.

When I first came to use DBAPI, I was taken aback by how it has no begin(), even though I had spent many years using Oracle, but I came to be used to it.    I'd be curious to know the original rationale for it to be that way.   If DBAPI only allowed transactions via begin() and commit(), that would be fine also, but its doubtful the current mode of operation could ever be removed.


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

Re: autocommit support in pep-249

M.-A. Lemburg
In reply to this post by Chris Clark-2
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.

--
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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

M.-A. Lemburg
In reply to this post by Michael Bayer
Michael Bayer wrote:

>
> On Sep 13, 2011, at 2:39 PM, Joshua D. Drake wrote:
>
>>
>> Oh now that is interesting. Hmmm... perhaps conn.autocommit() is actually a synonym for conn.begin() except that it sets the autocommit property? So we would have two semantics:
>>
>> conn.begin() -- Starts a transaction, will not commit unless explicit conn.commit() is called.
>>
>> conn.autocommit() -- Works in autocommit mode, calling conn.commit() is redundant, each statement is executed as it is delivered to the database.
>
> I'm -1 on conn.begin().   I've spent years explaining to users the beauty of the DBAPI in that it has no "begin" method.   Providing for two different modes of doing the same thing, i.e. working with a standard DBAPI connection in a transaction, or forcing autocommit to True then using begin(), creates for a more confusing and controversial interface.  

-1 on connection.begin() as well.

DB-API modules are free to implement this, but it should not be
part of the DB-API standard, since it causes rather complicated
transactional/non-transactional states on the connection.

The simple implicit transaction start when opening a connection
or doing a commit/rollback is much less error prone and protects
the user from data corruption in error situations.

--
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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Federico Di Gregorio-6
In reply to this post by Chris Clark-2
On 13/09/11 18:39, Chris Clark wrote:
[snip]

>>> The behaviour of setting autocommit when a transaction is already open
>>> is also to be seen: there are three basic option:
>>>
>>> - have a commit
>>> - have a rollback
>>> - have an error
>>>
>>> I feel the implicit commit is the most dangerous option. An implicit
>>> rollback may be more acceptable (it's the same behaviour of closing
>>> the connection with a transaction in progress): I proposed it for
>>> psycopg but, in the discussion, raising an exception proved the most
>>> popular option.
>>>
>>>    
>> I don't think I know how to even determine whether there is a
>> transaction in progress in ADO.(*)  If there is a way, then checking
>> on it just so that I can raise an exception seems expensive.  Remember
>> that most of the time the resulting stack trace would not be seen by
>> the programmer who goofed up, but a completely innocent user -- and to
>> her it looks like the program just crashed for no reason.  The
>> rollback would be easy to program and user friendly.  I vote rollback.
>>  
>
> The exception route has the potential to be the easiest for most DBMS
> implementations, most DBMSs will raise an error, so the driver doesn't
> need to track state and can just propagate the DBMS error. For some
> backends, state tracking would be required which is why I suggested the
> "easy" commit option. Rollback seems semi safe BUT when setting
> autocommit to ON the default behavior of a rollback seems a little
> surprising.
>
> I'm not in love with committing when the autocommit state is changed but
> for the "enable auto commit" operation it seems logical, the user is
> requesting commits take place under the covers. Conversely when
> disabling autocommit (after it has previously been enabled) we are
> either not in a transaction (due to auto commit being on) or we could
> still be fetching mid select, in which case a commit would be the same
> as  a rollback. I.e. only impact on locking, any writes to the database
> would have been already auto committed.
[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

--
Federico Di Gregorio                                       [hidden email]
 If nobody understand you, that doesn't mean you're an artist.
                                                            -- anonymous
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Chris Clark-2
In reply to this post by M.-A. Lemburg
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'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.

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


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.

Chris

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

Re: autocommit support in pep-249

Joshua D. Drake
In reply to this post by Michael Bayer

On 09/13/2011 09:12 PM, Michael Bayer wrote:

>
>
> On Sep 13, 2011, at 9:15 PM, Joshua D. Drake wrote:
>
>>
>> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
>> cur = conn.cursor()
>> cur.execute("""INSERT""")
>> cur.execute("""SELECT * from bar""")
>> cur.begin()
>> cur.execute("""INSERT""")
>> cur.commit()
>> cur.close()
>>
>> Is much better than:
>>
>> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
>> cur = conn.cursor()
>> cur.execute("""INSERT""")
>> cur.execute("""SELECT * from bar""")
>> cur.execute("""BEGIN""")
>> cur.execute("""INSERT""")
>> cur.commit()
>> cur.close()
>>
>
> what's wrong with:
>
> conn = psycopg2.connect(....)
> cur = conn.cursor()
> cur.execute(" ... sql ...")
> conn.commit()

Nothing, that doesn't actually do anything I didn't suggest above.

>
> ?   if one wants to work with transactions, DBAPI in it's default mode provides that.

Correct.

>     If you want all statements to be ad-hoc such that they are "committed" immediately, whether that means COMMIT after every statement as it does on Oracle or just no BEGIN emitted as it does on Postgresql, turn on "autocommit".   "autocommit" is a flag you should only be able to change when no transactional state has otherwise accumulated, but otherwise, switch it any time.

Yes.

>
> Adding explicit begin() means the DBAPI starts to look confused as to how it should be used - DBAPI authors also have to support two different transactional styles.

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.

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
|  
Report Content as Inappropriate

Re: autocommit support in pep-249

Chris Clark-2
Joshua D. Drake wrote:

>
> On 09/13/2011 09:12 PM, Michael Bayer wrote:
>>
>>
>> On Sep 13, 2011, at 9:15 PM, Joshua D. Drake wrote:
>>
>>>
>>> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
>>> cur = conn.cursor()
>>> cur.execute("""INSERT""")
>>> cur.execute("""SELECT * from bar""")
>>> cur.begin()
>>> cur.execute("""INSERT""")
>>> cur.commit()
>>> cur.close()
>>>
>>> Is much better than:
>>>
>>> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
>>> cur = conn.cursor()
>>> cur.execute("""INSERT""")
>>> cur.execute("""SELECT * from bar""")
>>> cur.execute("""BEGIN""")
>>> cur.execute("""INSERT""")
>>> cur.commit()
>>> cur.close()
>>>
>>
>> what's wrong with:
>>
>> conn = psycopg2.connect(....)
>> cur = conn.cursor()
>> cur.execute(" ... sql ...")
>> conn.commit()
>
> Nothing, that doesn't actually do anything I didn't suggest above.
>
>>
>> ?   if one wants to work with transactions, DBAPI in it's default
>> mode provides that.
>
> Correct.
>
>>     If you want all statements to be ad-hoc such that they are
>> "committed" immediately, whether that means COMMIT after every
>> statement as it does on Oracle or just no BEGIN emitted as it does on
>> Postgresql, turn on "autocommit".   "autocommit" is a flag you should
>> only be able to change when no transactional state has otherwise
>> accumulated, but otherwise, switch it any time.
>
> Yes.
>
>>
>> Adding explicit begin() means the DBAPI starts to look confused as to
>> how it should be used - DBAPI authors also have to support two
>> different transactional styles.
>
> 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.

I think the semantics you are describing are:

   1. auto commit mode is on
   2. whilst in auto commit mode the user wants to start a transaction
      that will not be autocommit

The above is a guess I'm making, ;-) Those semantics are very different
to other DBMS implementations and maybe why there is some confusion over
the suggestion.

Chris


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

Re: autocommit support in pep-249

Michael Bayer
In reply to this post by Joshua D. Drake

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.

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
12
Loading...