Skip site navigation (1) Skip section navigation (2)

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

From: Vlad <marchenko(at)gmail(dot)com>
To: Greg Sabino Mullane <greg(at)turnstep(dot)com>
Cc: dbdpg-general(at)gborg(dot)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [Dbdpg-general] Re: 'prepare' is not quite schema-safe
Date: 2005-05-03 12:25:08
Message-ID: cd70c6810505030525cd0d895@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Pavel StehuleDate: 2005-05-03 12:29:32
Subject: Re: some questions : psql
Previous:From: Nikhil ParvaDate: 2005-05-03 12:23:01
Subject: Re: REMOVE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group