no support for true autocommit

[J Pellerin]

I haven't had much success trying to explain my case for why and how I think autocommit support should be improved, so here's an example, with postgres query log and sa query log, that I think pretty clearly shows that when you put a connection into autocommit mode, you:

• get different SQL executed on the db even though you run the same python code
• can't rely on transactions to work, since they never issue BEGIN

I think the solution to this is to:

• provide a standard way to get and set true autocommit mode
• implement do_begin to get a cursor, issue cursor.execute('BEGIN') and use that cursor for the rest of the transaction (until connection.rollback() or connection.commit() when autocommit mode is on)

I'm volunteering to work on those changes in a branch if there's interest

[J Pellerin]

Example of autocommit mode with psycopg2

[J Pellerin]

sqlalchemy query log from run of example.py

[J Pellerin]

Annotated postgres query log from run of example.py

[Mike Bayer]

its not news that turning on autocommit on a connection breaks SA's ORM. thats because we dont issue a begin, which is because ''dbapi has no begin()''. that you can hack it on exactly one database (i.e. postgres) using the string "BEGIN" doesnt lend much to an argument in favor of it...this behavior would not work on any other database (its been tried).

the method that DBAPI provides of beginning a transaction is to turn off autocommit mode, and a transaction is then always in progress (or at least after the first statement after a previous commit()). that the authors of dbapi, version 2 no less, did not feel it was important to put a begin() method alongside commit and rollback, combined with the fact that psycopg2, which has tons of PG-specific extensions like server/client side cursors, named cursors, array types, etc. also does not implement begin(), its clear that the authors of dbapi/psycopg2 were following the rule of "there should preferably be only one way to do it", and i think they made the right choice.

so beyond that, i still dont see any use case for putting "autocommit" on the connection itself when SA already takes care of ad-hoc CRUD operations for you...that is what you have to show me. if theres some huge performance issue, then submit a test case that proves it. a single BEGIN issued at the top of a connection and after each COMMIT is inconsequential.

even if SA is modified to support connections that have "autocommit" turned on, it would still just turn the autocommit off when its time to begin a transaction...since that is how DBAPI/psycopg2 intend for transactions to be used. psycopg2 then issues the BEGIN for you.

[Mike Bayer]

quietly closing this one for now...