Re: [Dbdpg-general] Re: 'prepare' is not quite schema-safe

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


>> Which is why Vlad should use prepare() instead of prepare_cached().

> in our web application similar SQL queries (like load an object)
> executed over and over again with high frequency. So it's very
> desirable to use prepare_cached. I think we are going to adjust our
> ORM (object relation mapper) to always use full path to reference DB
> objects in query.

This is actually a perfect case for prepare (and server-side prepare at
that), and not prepare_cached(). The latter has some overhead as a hash
table has to be searched and the right statement handle produced. One thing
I sometimes do is "pre-prepare" a lot of my frequently used statements at
the top of a long-running script (e.g. mod_perl). Then you simply refer to
the statement handle rather than prepare() or prepare_cached. It also has the
advantage of consolidating most of your SQL calls into one place in your
script. You can even create different handles for changed schemas.
It goes something like this:

#!pseudo-perl

BEGIN {
  ## mod_perl only runs this once
  use DBI;
  my %sth;
  $dbh = DBI->connect...

  ## Grab a user's information
  $SQL = "SELECT * FROM u WHERE status = 2 AND username=?";
  $sth{grabuser} = $dbh->prepare($SQL);

  ## Insert a widget
  $SQL = "INSERT INTO widgets(partno, color) VALUES (?,?,?)";
  $sth{addwidget} = $dbh->prepare($SQL);
  ## Insert a widget into the jetson schema
  $dbh-do("SET search_path TO jetson");
  $sth{addwidget_jetson} = $dbh->prepare($SQL);

  ## (reset search_path, keep going with all common SQL statements)

}
## mod_perl runs all this each time:

...skip lots of code...

my $username = $forminput{'username'};
$sth = $sth{grabuser};
$count = $sth->execute($username);

...and later on...

for (@widgets) {
  if ("jetson" eq $_->{owner}) {
    $dbh->do("SET search_path TO jetson");
    $sth{addwidget_jetson}->execute($_->{partnumber}, $_->{color});
    $dbh->do("SET search_path TO public");
    ## Silly example, better to use fully qualified names of course,
    ## or perhaps a custom function that inserts for you
  }
  else {
    $sth{addwidget}->execute($_->{partnumber}, $_->{color});
  }
}

A simplified example, but the take home moral of all this is to be very
careful when using prepare_cached (which is actually a DBI feature, not
a DBD::Pg one).

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200505011119
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCdPSrvJuQZxSWSsgRAsntAJ4iqrfqkj/f5Dqc4Ya7Vs4h0XZhGwCgxC15
mM86zvTH/mXdAACBKPDG//4=
=vZ2+
-----END PGP SIGNATURE-----