View Issue Details

IDProjectCategoryView StatusLast Update
0000020Pgpool-IIBugpublic2012-11-26 16:00
Reportersingh.gurjeetAssigned Tot-ishii 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionreopened 
PlatformLinux 64bitOSUbuntuOS Version12.04
Product Version 
Target VersionFixed in Version 
Summary0000020: pgpool does not accept connections after postgres hits the max_connections limit
DescriptionI have reported this bug to -hackers list [1], Repeating it here for the follow-up.

    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.

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.

[1] http://www.sraoss.jp/pipermail/pgpool-hackers/2012-August/000102.html
Steps To ReproduceHere'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)$

TagsNo tags attached.

Activities

singh.gurjeet

2012-08-01 01:00

reporter  

pgpool_bug_report.tgz (5,323 bytes)

t-ishii

2012-08-17 08:12

developer   ~0000044

Did you follow the guide in the doc?

   In summary, max_pool, num_init_children, max_connections,
   superuser_reserved_connections must satisfy the following formula:

   max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)

   max_pool*num_init_children*2 >= (max_connections - superuser_reserved_connections) (query canceling needed)

Issue History

Date Modified Username Field Change
2012-08-01 01:00 singh.gurjeet New Issue
2012-08-01 01:00 singh.gurjeet File Added: pgpool_bug_report.tgz
2012-08-17 08:12 t-ishii Note Added: 0000044
2012-08-17 08:17 t-ishii Status new => resolved
2012-11-26 16:00 t-ishii Assigned To => t-ishii
2012-11-26 16:00 t-ishii Status resolved => feedback
2012-11-26 16:00 t-ishii Resolution open => reopened
2012-11-26 16:00 t-ishii Status feedback => resolved