Perl and Rose::DB Postgres Transactions

Question

I've been struggling with this problem all day.. thought I would ask here, before I loose all my hair.

Description

I'm using Perl 5.10.1 with Rose::DB and PostgreSQL 8.4 (on Debian Linux).

I need some changes to my "trans" objects to be within a single transaction block (i.e. the changes are either all written, or rolled back). However, I just cannot get it to work.

I have tried it with both AutoCommit ON and OFF.

In the sample code below, $db is the Rose::DB connection established at the start of the script (using: my $db = My::DB->new;). All Rose::DB objects inherit from a base class (My::Base). This base class has an inheritable sub for the DB connection:

sub init_db
{
    My::DB->new_or_cached
}

The DB connection object (My::DB) contains the connection strings and the settings:

_ _PACKAGE_ _->use_private_registry;

_ _PACKAGE_ _->register_db(
    driver          => 'pg',

database        => 'xx',
host            => 'localhost',
username        => 'xx',
password        => 'xx',

    connect_options => {
        AutoCommit      => 0,  -- changed to suit SCENARIO 1 and 2 below
        RaiseError      => 1,
    }
);

SCENARIO 1: AutoCommit OFF

AutoCommit 0 and RaiseError 1

my $trans = shift;

eval {
    $trans->...        -- Make changes to object
    $trans->save;

    # die "testing";   -- Cause a rollback using "die"

    $db->commit or die $db->error;
};

if ($@)
{
    warn "aborted: $@";

    eval {
        $db->rollback;
    };
}

Rollback case: Works (no changes written to DB)

Commit case : Fails (no changes written to DB)

SCENARIO 2: AutoCommit ON

AutoCommit 1 and RaiseError 1

my $trans = shift;

eval {
    $db->begin_work or die $db->error;

    $trans->...        -- Make changes to object
    $trans->save;

    # die "testing";   -- Cause a rollback using "die"

    $db->commit or die $db->error;
};

if ($@)
{
    warn "aborted: $@";

    eval {
        $db->rollback;
    };
}

Rollback case: Fails (changes written to DB)

Commit case : Works (changes written to DB)

Any help or advice you can offer would be greatly appreciated.

Thanks in advance.

Answer 1

You may be opening more than one database connection. The behavior is exactly what one would expect if the connection that you commit is not the same connection that has the transaction.

I suggest you re-examine your program flow to make sure that you only have a single connection .