rollback ineffective with AutoCommit enabled in do_transaction?

[prev] [thread] [next] [Date index for 2005/05/20]

I'm seeing a warning when using the do_transaction idiom with my 
PostgreSQL database. When a transaction fails, I see the following:

rollback ineffective with AutoCommit enabled at 
/usr/local/share/perl/5.8.3/Ima/DBI.pm line 590.
Can't insert new Foo: DBD::Pg::st execute failed: ERROR:  duplicate 
key violates unique constraint "foo_pkey"
...

It seems like this isn't actually a problem because the transaction 
is being rolled back automatically because of the duplicate key 
collision, but why is AutoCommit enabled? Am I just not seeing why 
that local declaration is going out of scope?


Should I just leave out the if ( $@ ) ... part of the do_transaction 
because my database is automatically rolling back?

The "warn"s tell me that AutoCommit is back at point 4 in the test code below.

1 AC: 1
2 AC:
3 AC:
4 AC: 1
5 AC: 1


I'm using Perl 5.8.4, C::DBI 0.96 and Ima::DBI 0.33 against 
PostgreSQL 7.4.7, if that's relevant.


Here's my database:

create table foo ( id integer primary key );


And my test case:

package CDBI;

use base 'Class::DBI';
use Class::DBI::Plugin::RetrieveAll;

__PACKAGE__->connection('dbi:Pg:dbname=loath', '', '', {AutoCommit => 1});


sub do_transaction {
     my $class = shift;
     my ( $code ) = @_;
     # Turn off AutoCommit for this scope.
     # A commit will occur at the exit of this block automatically,
     # when the local AutoCommit goes out of scope.
     warn "1 AC: " . $class->db_Main->{ AutoCommit };
     local $class->db_Main->{ AutoCommit };
     warn "2 AC: " . $class->db_Main->{ AutoCommit };

     # Execute the required code inside the transaction.
     eval { warn "3 AC: " . $class->db_Main->{ AutoCommit };
	   $code->() };
     if ( $@ ) {
	my $commit_error = $@;
	warn "4 AC: " . $class->db_Main->{ AutoCommit };
	eval { warn "5 AC: " . $class->db_Main->{ AutoCommit };
	       $class->dbi_rollback }; # might also die!
	die $commit_error;
     }
}

package Foo;

use base 'CDBI';

__PACKAGE__->table('foo');
__PACKAGE__->columns(All => qw/id/);

__PACKAGE__->do_transaction(sub {
				__PACKAGE__->create({id => 1});
				__PACKAGE__->create({id => 1});
			    });

        -- 
        Aneel Nazareth -- http://eye-of-newt.com/nazareth --