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 greg@turnstep.com
End Point Corporation http://www.endpoint.com
PGP Key: 0x14964AC8 200608051821