perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Sabino Mullane" <>
Subject Re: issue with DBD::Pg, server side prepares, and persistent connections?
Date Sat, 05 Aug 2006 22:27:07 GMT

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 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
End Point Corporation
PGP Key: 0x14964AC8 200608051821


View raw message