[pgpool-hackers: 103] pgpool does not accept connections after postgres hits the max_connections limit

[Gurjeet Singh]

Hi,

    As per the docs:

   - *Limiting Exceeding Connections*

 There is a limit on the maximum number of concurrent connections with
PostgreSQL, and connections are rejected after this many connections.
Setting the maximum number of connections, however, increases resource
consumption and affect system performance. pgpool-II also has a limit on
the maximum number of connections, but *extra connections will be queued
instead of returning an error immediately.*

So one would expect the application to wait indefinitely if the connection
parameter pair (user, database) that it is requesting is not yet available,
but I see that the application connections are terminated immediately.
Here's how I tested this on version 3.1.3 and master branch ( 3.2.0 RC3,
commit id: 1c584a84).
.) Start a single Postgres database instance (version PostgreSQL 9.1.4)
.) Create 2 databases : test and test2
.) Start pgpool as follows: (config files attached)

inst/bin/pgpool -f ~/Downloads/pgpool.conf -F ~/Downloads/pcp.conf -a
~/Downloads/pool_hba.conf

.) In one terminal, run psql clients, one at a time, to connect to 'test'
database, over pgpool:

export PGHOST=localhost PGPORT=9999 PGDATABASE=test PGUSER=postgres
while sleep 1; do psql -c "select datname, now()::timestamp(0), count(*)
from pg_stat_activity group by datname"; done

.) In other terminal, run psql clients, one at a time,, to connect to
'test2' database over pgpool:

export PGHOST=localhost PGPORT=9999 PGDATABASE=test2 PGUSER=postgres
while sleep 1; do psql -c "select datname, now()::timestamp(0), count(*)
from pg_stat_activity group by datname"; done

After a few seconds, in the second terminal, when the total connections on
Postgres server have all been exhausted, I see the psql connections failing:


 datname |         now         | count
---------+---------------------+-------
 test    | 2012-07-31 11:17:49 |    75
 test2   | 2012-07-31 11:17:49 |    25
(2 rows)

psql: psql: psql: psql: psql: psql: psql: psql: psql: psql: psql: psql:
psql: psql: psql: psql: psql: psql: psql: psql: ^C
gurjeet at work:~/dev/POSTGRES T111809 (pg_9.1_stable)$

Given that pgpool advertises that 'extra connections will be queued instead
of returning an error immediately", I think we should treat this as a bug,
unless there's something wrong with my configuration.

Best regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120731/fe8cc7ec/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.conf
Type: application/octet-stream
Size: 9803 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120731/fe8cc7ec/attachment-0003.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pool_hba.conf
Type: application/octet-stream
Size: 3336 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120731/fe8cc7ec/attachment-0004.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pcp.conf
Type: application/octet-stream
Size: 895 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120731/fe8cc7ec/attachment-0005.obj>