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

Greg,

thanks for the suggestion. looking into other thread on the list, it
looks like setting

$dbh->{pg_server_prepare} = 0;

would solve my problem as well. With this setting will dbd::pg behave
in old-style (i.e. prepare_cached prepared and stored on dbd::pg
side), or it won't cache anything at all?
Besides, why don't you recommend turning pg_server_prepare off?


On 5/2/05, Greg Sabino Mullane <greg@turnstep.com> wrote:
>
> -----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-----
>
>
> _______________________________________________
> Dbdpg-general mailing list
> Dbdpg-general@gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/dbdpg-general
>


--

Vlad