Re: Perl DBI, PostgreSQL performance question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Been offline for a while, so I can't tell for sure if this was
addressed before when someone posted a similar "eval and catch"
scheme for Perl DBI (and specifically for DBD::Pg).

The short of it is, you do not need to use evals. Ever! Just
set commit off when connecting (AutoCommit => 0), and postgres
will not commit until you specifically tell it to.
On any error* it will rollback. If the script exits normally
without $dbh->commit() being called, it will rollback.

*any error that causes the script to die or otherwise prevents
the $dbh->commit() statement from being called, to be precise.

Here is a short example:

#!perl

use DBI;

my $DBNAME = "test";
my $DBPORT = 5432;
my $DBUSER = "thelma";
my $DBPASS = "lcB%g^22_db nM";

my $dbh = DBI->connect("dbi:Pg:dbname=$DBNAME;port=$DBPORT",
                       $DBUSER, $DBPASS,
                       { AutoCommit=>0, RaiseError=>0, PrintError=>0})
  or &Error(1); ## Custom error handling, eventually calls 'die'

my $NEWUSER_SQL =
  "INSERT INTO users (uid,first,last,shoesize) VALUES (?,?,?,?)";
my $newuser_h = $dbh->prepare($NEWUSER_SQL)
  or &Error(1, $NEWUSER_SQL);
my @sqlargs = (12,"wanda", "mae", "9 3/4");
$newuser_h->execute(@sqlargs)
  or &Error(1, $NEWUSER_SQL, \@sqlargs);

## If the script died at any point above, "wanda mae" will not
## be in the database. If the "exit" line below is uncommented,
## "wanda mae" will not be in the database.

#exit;

$dbh->commit(); ## Now she is in there!

$dbh->rollback(); ## This has no effect whatsoever


Note that not all the DBD modules are guaranteed to exhibit
the "rollback on failure" feature, but luckilty for us,
postgresql does. :)

HTH,

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200112141116


-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8GiYtvJuQZxSWSsgRAj76AKCi4bMv/a7J07hSbZ/b0WQwL3BCRwCgvKtZ
CXAbjr5OzR+mxU0wE8Pz0vE=
=Wf40
-----END PGP SIGNATURE-----