DBI commit and rollback with postgesql DBD

ruby-talk
1

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.

···

–
tom sawyer, aka transami
transami@transami.net

2

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

···

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.

#!/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)

3

thanks Jim. wow! 8-o did you hack that out solely in repsonse to this email?
impressive.

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

it must be something to do with this. i need to test and see. i have been
using the hash parameter, i.e. DBI.connect(DSN, DBUSER, DBPW, ‘AutoCommit’ =>
false), but additionally i have been passing it through a secondary method,
ex-

def make_connection(dsn,dbu,dbpw,*args)
	DBI.connect(dsn,dbu,dbpw,*args)
end

perhaps i misunderstand *args in relation to hash arguments and this is
fouling up the hash parameter somehow?

i’ll test and see what i come up with. if i don’t get anywhere i’ll run your
test case and see what i get on my end.

thanks again,
transami

···

On Saturday 08 February 2003 06:09 pm, Jim Weirich wrote:

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

–
tom sawyer, aka transami
transami@transami.net

4

thanks Jim. wow! 8-o did you hack that out solely in repsonse to this email?
impressive.

It just took a few minutes.

Make sure you do …

db[‘AutoCommit’] = false

it must be something to do with this. i need to test and see. i have been
using the hash parameter, i.e. DBI.connect(DSN, DBUSER, DBPW, ‘AutoCommit’ =>
false), but additionally i have been passing it through a secondary method,
ex-

Hmmmm … I’ve never tried to pass parameters that way, but I’ve only
used the Postgresql DBD driver from the DBI package. Do other DBD
drivers set AutoCommit in that way?

Trying with connect call parameters …

$irb --simple-prompt

db = DBI.connect(“dbi:pg:jim”, DBUSER, DBPW, ‘AutoCommit’=>false)
=> [… elided …]
db[‘AutoCommit’]
=> true

It seems the Postgresql driver does not recognize autocommit in the
connect call. If people expect that, it should be fixed.

···

On Sat, 2003-02-08 at 22:27, Tom Sawyer wrote:

On Saturday 08 February 2003 06:09 pm, Jim Weirich wrote:

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

5

It just took a few minutes.

only a few minutes? very skilled! do you code for a living?

Hmmmm … I’ve never tried to pass parameters that way, but I’ve only
used the Postgresql DBD driver from the DBI package. Do other DBD
drivers set AutoCommit in that way?

Trying with connect call parameters …

$irb --simple-prompt

db = DBI.connect(“dbi:pg:jim”, DBUSER, DBPW, ‘AutoCommit’=>false)

=> [… elided …]

db[‘AutoCommit’]

=> true

It seems the Postgresql driver does not recognize autocommit in the
connect call. If people expect that, it should be fixed.

yep, that’s it. works now. i got the hash parameter method from the Ruby
Developer’s Guide…one of the two needs fixing.

thanks Jim, really appretiate your help.

okay, on to the next bug…(see tom run away like a super hero set to fly! :slight_smile:

···

On Sunday 09 February 2003 12:26 am, Jim Weirich wrote:

–
tom sawyer, aka transami
transami@transami.net