Bug #20648 for DBD-Pg: Server side prepares fail on load-balanced web servers.

Mon Jul 24 12:32:32 2006 MARKSTOS [...] cpan.org - Ticket created

Subject:

Server side prepares fail on load-balanced web servers.

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. Mark

Fri Aug 04 19:09:34 2006 MARKSTOS [...] cpan.org - Correspondence added

From:

MARKSTOS [...] cpan.org

On Mon Jul 24 12:32:32 2006, MARKSTOS wrote: Show quoted text

> 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

Sun Aug 06 22:47:17 2006 blblack [...] gmail.com - Correspondence added

From:

BLBLACK [...] cpan.org

On Fri Aug 04 19:09:34 2006, MARKSTOS wrote: Show quoted text

> 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 can't know what your exact issue is remotely, but all the times in the past that I've encountered those errors, it has always been a case of two seperate processes or threads sharing the same database connection (socket), for some reason or other. In a mod_perl situation, this could happen if your are preloading a module at pre-fork time, and then the individual forked children are all accessing the same connection that was created then. The solution tends to be to make sure you are disconnected from the database before forking (let each child connect for itself). If that sounds like too many connections, then you can look into any of the available ways to pool and share connections properly - but sharing a connection blindly via forking will always cause problems. DBIx::Class's storage driver handles all this sort of stuff for you (forking, threading, server disconnecting due to idle timeouts, etc), but when using a raw DBI driver handle, you have to do this stuff yourself.

Sun Aug 06 22:47:18 2006 The RT System itself - Status changed from 'new' to 'open'

Mon Aug 07 08:09:12 2006 mark [...] summersault.com - Correspondence added

Subject:	Re: [rt.cpan.org #20648] Server side prepares fail under mod_perl
Date:	Mon, 07 Aug 2006 08:05:12 -0500
To:	bug-DBD-Pg [...] rt.cpan.org
From:	Mark Stosberg <mark [...] summersault.com>

Show quoted text

>>> prepared statement "dbdpg_7" already exists

Thanks for the response. Show quoted text

> I can't know what your exact issue is remotely, but all the times in the > past that I've encountered those errors, it has always been a case of > two separate processes or threads sharing the same database connection > (socket), for some reason or other. > > In a mod_perl situation, this could happen if your are preloading a > module at pre-fork time, and then the individual forked children are all > accessing the same connection that was created then. The solution tends > to be to make sure you are disconnected from the database before forking > (let each child connect for itself).

In startup.pl, we load Apache::DBI, and DBI, but don't connect there. We fork in one place in the application, but explicitly use a second connection there, with the syntax that tells Apache::DBI not just re-use an existing connection. Show quoted text

> DBIx::Class's storage driver handles all this sort of stuff for you > (forking, threading, server disconnecting due to idle timeouts, etc), > but when using a raw DBI driver handle, you have to do this stuff

yourself. We are using a raw handle, so I could look at DBIx::Class is doing differently. I'll also try the patch which adds the current PID into the name, and see if that helps. Thanks again. Mark -- http://mark.stosberg.com/

Mon Jan 29 10:18:21 2007 MARKSTOS [...] cpan.org - Correspondence added

Subject:	more thoughts on "prepared statement...already exists" and $if_active.
From:	MARKSTOS [...] cpan.org

Just a note for future people who have a symptom like this. I recently got errors like /after/ applying the patch that will appear in 1.50. the errors looked like: prepared statement "dbdpg_12456_7" already exists I believe the cause in this case what that I had accidentally shared the database handle across a modperl fork, which should always be avoided. After I repaired that issue, these errors seemed to clear up. See also this related "wish" report for DBI: http://rt.cpan.org/Ticket/Display.html?id=24544 DBI supports an "if active" attribute which can be used to gracefully recover from this error, but the interface for this option is currently awkward to use, and it is not possible to easily set a default behavior for it.

Mon Jan 07 00:28:48 2008 greg [...] turnstep.com - Correspondence added

This is going to become more prevalent as things like pg_bouncer take off. Best solution is to simply set pg_server_prepare to always be off. See: http://people.planetpostgresql.org/greg/index.php?/archives/110-Using-DBDPg-with-pg_bouncer.html

Mon Jan 07 00:29:30 2008 greg [...] turnstep.com - Status changed from 'open' to 'resolved'