[DB-SIG] 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