From: Jim Weirich <jweirich@...>
Date: 2002-10-17T12:35:28+09:00
Subject: Re: DBI:Pg 'autocommit' is not a valid option name

On Wed, 2002-10-16 at 19:05, Michael Schuerig wrote:

> The first problem is that I can't convince Pg to switch autocommit mode 
> off. All I get is "'autocommit' is not a valid option name", no matter 
> if I do it via DBI or in psql.

This works for me with DBI ...

    dbh['AutoCommit'] = false

> DatabaseHandle#transaction doesn't work as is and I need to add explict 
> BEGIN/END statements:
> 
> DBI.connect(dbrc.dsn, dbrc.user, dbrc.password) do |dbh|
>   dbh.transaction do |dbh|
>     dbh.do "BEGIN"
>     dbh.do "INSERT ..."
>     raise "Don't commit!!!"
>     dbh.do "END"
>   end
> end

Interesting ... I ran into a similar problem just today.  I wasn't using
the Pg DBD, so details might be different.

The key is that when the DB handle is in auto-commit mode, it will not
automatically insert the BEGIN/END statements.  The transaction method
should (but doesn't) disable auto-commit internally.

My work around was to do this ...

   def f(db)
     ac = db['AutoCommit']
     db['AutoCommit'] = false
     db.transaction {
       # ... do database stuff ...
     }
   ensure
     db['AutoCommit'] = ac
   end

Of course, this could be refactored into a alternate version of
transaction where the "do database stuff" is a yield.  

> This is the first time I'm playing with DBI and I'm a Pg newbie, too. 
> So take the following with a shovel of salt.
> 
> I propose to change dbi.rb in the following way

[... proposed changes to DBI elided ...]
 
> Possibly these methods need to (or should) take into account the state 
> of AutoCommit.

Its been a while since I looked at the Pg DBD driver, but it might be
enough to turn off auto-commit during the transaction.  But I agree with
the general premise that "transaction" should work whatever the state of
auto-commit.

-- 
-- Jim Weirich     jweirich@one.net    http://w3.one.net/~jweirich
---------------------------------------------------------------------
"Beware of bugs in the above code; I have only proved it correct, 
not tried it." -- Donald Knuth (in a memo to Peter van Emde Boas)