<div dir="ltr">(By mistake, I sent this message to the old mailing list hosted on pgfoundry. This should have been either redirected to the new list, or at least an email should've been sent to notify that the list is not active anymore! Really inconvenient to realize a few days later that the message never got to the intended recipients.)<br>
<br>Here it to the new mailing list.<br><br><div class="gmail_quote">---------- Forwarded message ----------<br>From: <b class="gmail_sendername">Gurjeet Singh</b> <span dir="ltr"><<a href="mailto:singh.gurjeet@gmail.com">singh.gurjeet@gmail.com</a>></span><br>
Date: Tue, Jun 26, 2012 at 12:07 PM<br>Subject: pgpool unable to reuse cached connections for non-default databases, and special treatment of default databases<br>To: <a href="mailto:pgpool-hackers@pgfoundry.org">pgpool-hackers@pgfoundry.org</a><br>
<br><br><div dir="ltr"><div>I have noticed a bug that rears its head only when the client makes connections to a database whose name is not in one of 'postgres', 'template0', 'template1', or 'regression'.<br>
<br>This appears to be because of the following piece of code from child.c:<br><br></div><span style="font-family:courier new,monospace"> /*<br> * do not cache connection if:<br> * pool_config->connection_cahe == 0 or<br>
* database name is template0, template1, postgres or regression<br> */<br> if (pool_config->connection_cache == 0 ||<br> !strcmp(sp->database, "template0") ||<br> !strcmp(sp->database, "template1") ||<br>
!strcmp(sp->database, "postgres") ||<br> !strcmp(sp->database, "regression"))<br> {</span><br><br>So I see two problems with this.<br><br>One, these databases will never see the benefit of connection caching. Since most pgpool users would be using the default 'postgres' database, this seems to be a bad decision.<br>
<br>Second, the client connections that used a database not in this list, seem not to be able to reuse the cached connections unless the connections held by pgpool come close the the max_connection parameter in postgres.<br>
<br>Here's how to reproduce this:<br><br>export PGHOST=localhost PGPORT=9999 PGDATABASE=some_other_db PGUSER=postgres<br><br>while sleep 1; do psql "select 1"; done<br><br>This causes the number of connections on postgres side to steadily increase, even though the client app disconnects before requesting a new connection.<br>
<br>If I change the PGPORT to 5432, then the number of connections stays stable at 1. And if I change the PGDATABASE to 'postgres' (keeping PGPORT=9999), then also the number of connections on postgres stay stable.<br>
<br>The first problem of treating some databases might be acceptable to some, but the inability to reuse a previously released connection troubles me. To be specific, this causes the c3p0 connection pooler (which wants to keep just 5 connections) to cause a connection spike on postgres, and hence those postgres backends are not usable by any other application.<br>
<br>I have tried to diagnose the second problem, but couldn't make any headway.<br><br>Reproducible on HEAD (commit 50a10bf) of the master branch.<br clear="all"><br>Best regards,<span class="HOEnZb"><font color="#888888"><br>
-- <br><div dir="ltr">Gurjeet Singh<br>
EnterpriseDB Corporation<br>The Enterprise PostgreSQL Company<br></div><br>
</font></span></div>
</div><br><br clear="all"><br>-- <br><div dir="ltr">Gurjeet Singh<br>EnterpriseDB Corporation<br>The Enterprise PostgreSQL Company<br></div><br>
</div>