[pgpool-hackers: 77] Fwd: pgpool unable to reuse cached connections for non-default databases, and special treatment of default databases

[Gurjeet Singh]

(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.)

Here it to the new mailing list.

---------- Forwarded message ----------
From: Gurjeet Singh <singh.gurjeet at gmail.com>
Date: Tue, Jun 26, 2012 at 12:07 PM
Subject: pgpool unable to reuse cached connections for non-default
databases, and special treatment of default databases
To: pgpool-hackers at pgfoundry.org


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

This appears to be because of the following piece of code from child.c:

    /*
     * do not cache connection if:
     * pool_config->connection_cahe == 0 or
     * database name is template0, template1, postgres or regression
     */
    if (pool_config->connection_cache == 0 ||
        !strcmp(sp->database, "template0") ||
        !strcmp(sp->database, "template1") ||
        !strcmp(sp->database, "postgres") ||
        !strcmp(sp->database, "regression"))
    {

So I see two problems with this.

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.

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.

Here's how to reproduce this:

export PGHOST=localhost PGPORT=9999 PGDATABASE=some_other_db PGUSER=postgres

while sleep 1; do psql "select 1"; done

This causes the number of connections on postgres side to steadily
increase, even though the client app disconnects before requesting a new
connection.

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.

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.

I have tried to diagnose the second problem, but couldn't make any headway.

Reproducible on HEAD (commit 50a10bf) of the master branch.

Best regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company




-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120628/185737d3/attachment.html>