[DB-SIG] autocommit support in pep-249

[Chris Clark]

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