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

[Tatsuo Ishii]

> (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!

I just don't know how to do it.

> 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,

I don't believe this and have never heard about such a user in my
customers who is using "postgres" database to store mission critical
data. Any serious database users will never use "postgres" for their
real database. The postgres database should be soly used for
PostgreSQL internal use (for example autovacuum) or database
administration tool like createdb.

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

Probably you'd better to look into our FAQ.
Especially this:
"Is connection pool cache shared among pgpool process?"
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp