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.

Why does your Scenario 1 not have `$db->begin_work`? Are you sure that your two tests are equivalent? — , Feb 14 '13 at 08:44
In Scenario 1, we don't need the begin_work because we're not in AutoCommit mode. We're already in a transaction. :) — ritter, Feb 14 '13 at 18:34
I'm not sure there's enough of your code here to tell what is happening. Can you post a complete simplified example (including creating the connection) that replicates the problem? — , Feb 15 '13 at 07:55
One other comment: this behavior is exactly what you would get if you accidentally had more than one connection (if the connection that was committing was not the same connection that had the transaction). It's hard to say whether this is a possibility from just the code you have posted here, though. — , Feb 15 '13 at 07:58
Thanks for the idea Dan. This was definitely the problem. If you could create an answer using this last comment, I will select it and add some details. Cheers! — ritter, Feb 17 '13 at 22:32

score 2 · Accepted Answer · answered Feb 18 '13 at 08:26

2

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 .

answered Feb 18 '13 at 08:26

Hi Dan, Thanks for the suggestion. This was definitely the problem. Unless you specify a db connection in an object constructor, it will either make a new connection (or use an existing one). The solution is to specify the db handle in the constructor like so: $trans = My::Trans(db => $db); – ritter Feb 19 '13 at 03:34

Perl and Rose::DB Postgres Transactions

Description

SCENARIO 1: AutoCommit OFF

SCENARIO 2: AutoCommit ON

1 Answers1