This is a discussion on Re: issue with DBD::Pg, server side prepares, and persistent connections? - modperl ; -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Newer versions of DBD::Pg and PostgreSQL support a feature called > "server side prepares", which is supposed to give a significant > performance boost in some cases. > > However, when deploying it ...
-----BEGIN PGP SIGNED MESSAGE-----
> Newer versions of DBD::Pg and PostgreSQL support a feature called
> "server side prepares", which is supposed to give a significant
> performance boost in some cases.
> However, when deploying it on mod_perl on a busy website, I quickly saw
> a lot of this kind of error:
> prepared statement "dbdpg_7" already exists
Server-side prepare statements exists on a per-connection basis, and
have been causing some problems for applications that do funky things
with connections, such as pgpool. However, when mod_perl is involved,
the problem is usually caused by connecting to a database *before*
forking children, which is almost always a Bad Thing. In short, the
children have no way of coordinating with each other about the names
being used, as their parent dbh handles have been cloned.
I put a workaround for this problem into the next version of DBD::Pg -
it will be available in the upcoming version 1.50. If you want to try it
out before then, you can try patching up dbdimp.c yourself like so:
- - /* Name is simply "dbdpg_#" */
- - sprintf(imp_sth->prepare_name,"dbdpg_%d", imp_dbh->prepare_number);
+ /* Name is simply "dbdpg_PID_#" */
+ sprintf(imp_sth->prepare_name,"dbdpg_%d_%d", getpid(), imp_dbh->prepare_number);
I've still no idea how to cure the pgpool problem, but this will probably
help your mod_perl one. The other option is to simply turn off the
server-side prepares by adding pg_server_prepare => 0 to the connection
attributes, or via the database handle.
Greg Sabino Mullane firstname.lastname@example.org
End Point Corporation http://www.endpoint.com
PGP Key: 0x14964AC8 200608051821
-----BEGIN PGP SIGNATURE-----
-----END PGP SIGNATURE-----