[rt.cpan.org #20648] Server side prepares fail on load-balanced web servers.

       Queue: DBD-Pg
 Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=20648 >

On Mon Jul 24 12:32:32 2006, MARKSTOS wrote:
> Hello,
> 
> After deploying DBD::Pg 1.48 on a load-balanced web server talking to
> PostgreSQL 8.1, I saw a lot of these kind of errors:
> 
>  prepared statement "dbdpg_7" already exists
> 
> I suspect this is because DBD::Pg is on two different machines,
> talking to the same database, and each has different concept of
> what should be stored in 'dbdpg_7'.
> 
> If so, it would be nice if this could be avoided. 
> 
> Using a hash of the query itself would be a useful key, because that
> means it could originate on one of the front end web servers, 
> and then be re-used by one of the other ones.

I found a thread about this here. A couple of different other reasons
are reported. Sometimes the error is also appearing a modperl context,
sometimes not. No-one else was running on a load-balanced web server, so
I'll take that to mean my initial guess about the cause was wrong. 

http://gborg.postgresql.org/pipermail/dbdpg-general/2005-December/001926.html

I found a related mention of this issue here:
http://fudforum.org/forum/index.php?t=msg&th=4598&start=0&

There the fix involved putting using DEALLOCATE when persistent
connections are detected. The actual patch they used in their
application was here:

http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=10611

I don't see any calls to DEALLOCATE in the current DBD::Pg code base.
Maybe that could help? 
http://www.postgresql.org/docs/8.1/interactive/sql-deallocate.html

      Mark