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(at)turnstep(dot)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(at)turnstep(dot)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(at)gborg(dot)postgresql(dot)org
> http://gborg.postgresql.org/mailman/listinfo/dbdpg-general
>

--

Vlad