title: Perl and Rose::DB Postgres Transactions
body: <p>I've been struggling with this problem all day.. thought I would ask here, before I loose all my hair. </p>
  
  <h1>Description</h1>
  
  <p>I'm using Perl 5.10.1 with Rose::DB and PostgreSQL 8.4 (on Debian Linux). </p>
  
  <p>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.</p>
  
  <p>I have tried it with both AutoCommit ON and OFF.</p>
  
  <p>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:</p>
  
  <pre><code>sub init_db
  {
      My::DB-&gt;new_or_cached
  }
  </code></pre>
  
  <p>The DB connection object (My::DB) contains the connection strings and the settings:</p>
  
  <pre><code>_ _PACKAGE_ _-&gt;use_private_registry;
  
  _ _PACKAGE_ _-&gt;register_db(
      driver          =&gt; 'pg',
  
  database        =&gt; 'xx',
  host            =&gt; 'localhost',
  username        =&gt; 'xx',
  password        =&gt; 'xx',
  
      connect_options =&gt; {
          AutoCommit      =&gt; 0,  -- changed to suit SCENARIO 1 and 2 below
          RaiseError      =&gt; 1,
      }
  );
  </code></pre>
  
  <h1>SCENARIO 1: AutoCommit OFF</h1>
  
  <p>AutoCommit 0 and RaiseError 1</p>
  
  <pre><code>my $trans = shift;
  
  eval {
      $trans-&gt;...        -- Make changes to object
      $trans-&gt;save;
  
      # die "testing";   -- Cause a rollback using "die"
  
      $db-&gt;commit or die $db-&gt;error;
  };
  
  if ($@)
  {
      warn "aborted: $@";
  
      eval {
          $db-&gt;rollback;
      };
  }
  </code></pre>
  
  <p>Rollback case: Works (no changes written to DB)</p>
  
  <p>Commit case  : <strong>Fails</strong> (no changes written to DB)</p>
  
  <h1>SCENARIO 2: AutoCommit ON</h1>
  
  <p>AutoCommit 1 and RaiseError 1</p>
  
  <pre><code>my $trans = shift;
  
  eval {
      $db-&gt;begin_work or die $db-&gt;error;
  
      $trans-&gt;...        -- Make changes to object
      $trans-&gt;save;
  
      # die "testing";   -- Cause a rollback using "die"
  
      $db-&gt;commit or die $db-&gt;error;
  };
  
  if ($@)
  {
      warn "aborted: $@";
  
      eval {
          $db-&gt;rollback;
      };
  }
  </code></pre>
  
  <p>Rollback case: <strong>Fails</strong> (changes written to DB)</p>
  
  <p>Commit case  : Works (changes written to DB)</p>
  
  <p>Any help or advice you can offer would be greatly appreciated.</p>
  
  <p>Thanks in advance.</p>