From: Jim Weirich <jweirich@...>
Date: 2003-02-09T10:09:13+09:00
Subject: Re: DBI commit and rollback with postgesql DBD

On Sat, 2003-02-08 at 13:03, Tom Sawyer wrote:
> i was testing out transaction support with DBI against my postgresql DBD and 
> commit and rollback don't seem to be working. is this known? has this been 
> fixed recently or something? or is there some trick i'm missing? i turned 
> autocommit off (does that effect postgresql?) and used the transaction block, 
> and also tried to use commit and rollback manually. no go.

I have no problems using transaction in DBI and Postgresql.  I've
included the following Test::Unit testcase to demonstrate.  All of the
tests pass for me.

Make sure you do ...

  db['AutoCommit'] = false

before you start using transactions.  That's a common problem.

Unit test follows
-----------------------------------------------------------------------------------
#!/usr/bin/env ruby

# Unit tests to show transactions in DBI/Pg are working (or not).

require 'dbi'
require 'test/unit'
require 'dsn'

class TestTransaction < Test::Unit::TestCase
  def setup
    @db = DBI.connect(DSN, DBUSER, DBPW)
    @db['AutoCommit'] = false
    initialize_table
    @db.commit
  end

  def initialize_table
    @db.do("DELETE FROM testtransaction WHERE name=?", 'jim')
    @db.do("INSERT INTO testtransaction VALUES(?,?)",  'jim', 46)
  end

  def test_setup
    check_age(46)
  end

  def test_commit
    set_age(30)
    @db.commit
    check_age(30)
  end

  def test_rollback
    set_age(31)
    @db.rollback
    check_age(46)
  end

  def test_transaction
    @db.transaction do
      set_age(32)
    end
    check_age(32)
  end

  def test_transaction_rollback
    @db.transaction do
      set_age(33)
      @db.rollback
    end
    check_age(46)
  end

  def test_transaction_mid_rollback
    @db.transaction do
      set_age(33)
      @db.rollback
      set_age(34)
    end
    check_age(34)
  end

  def test_transaction_exception
    begin
      @db.transaction do
	set_age(35)
	fail "Abort!  Abort!"
      end
    rescue
    end
    check_age(46)
  end

  def test_transaction_throw
    catch(:abort) do
      @db.transaction do
	set_age(36)
	throw :abort
      end
    end
    check_age(36)
  end

  def set_age(age)
    @db.do("UPDATE testtransaction SET age=? WHERE name=?",
      age, 'jim')
  end    

  def check_age(age)
    row = @db.select_one("SELECT age FROM testtransaction")
    assert_equal age, row['age']
  end
end
--------------------------------------------------------------------

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