Debian Bug report logs - #630569
php5-pgsql and postgresql >= 8.4 seem to collude never to close idle persistent connections

Packages: postgresql-9.0, postgresql-8.4; Maintainer for postgresql-9.0 is (unknown); Maintainer for postgresql-8.4 is (unknown);

Reported by: Josip Rodin <joy@debbugs.entuzijast.net>

Date: Wed, 15 Jun 2011 10:03:02 UTC

Severity: important

Tags: moreinfo

Done: Christoph Berg <myon@debian.org>

Bug is archived. No further changes may be made.

Toggle useless messages

View this report as an mbox folder, status mbox, maintainer mbox

Report forwarded to debian-bugs-dist@lists.debian.org, debian-backports@lists.debian.org, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package php5-pgsql, postgresql-9.0. (Wed, 15 Jun 2011 10:03:05 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
New Bug report received and forwarded. Copy sent to debian-backports@lists.debian.org, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>.

Your message had a Version: pseudo-header with an invalid package version:

5.3.3-7+squeeze1, 9.0.4-1~bpo60+1

please either use found or fixed to the control server with a correct version, or reply to this report indicating the correct version so the maintainer (or someone else) can correct it for you.

(Wed, 15 Jun 2011 10:03:05 GMT) (full text, mbox, link).

Message #5 received at submit@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: submit@bugs.debian.org
Subject: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Wed, 15 Jun 2011 12:01:32 +0200
Package: php5-pgsql, postgresql-9.0
Version: 5.3.3-7+squeeze1, 9.0.4-1~bpo60+1
Severity: serious

Hi,

After the upgrade of one of our web servers to squeeze, coupled with
PostgreSQL 9 from squeeze-backports, the new pg_pconnect()-caused
connections seem to get created and left idling forever. When their number
eventually reaches PostgreSQL's max_connections limit, everything breaks.

With the lenny PostgreSQL 8.3, the squeeze php5-pgsql seems to be able to
spool normally, foregoing excess connections over time. But not this one.

(Obviously I brought this upon myself for trying a new non-Debian-stable
database version, but the new database version is in wheezy so it matters
for the next stable anyway.)

The calling code at this end is literally unchanged for years, it's
basically a pg_pconnect() call to a set of two hostnames that both resolve
into 127.0.0.1 via /etc/hosts. All PHP children connect to a variety of
local databases using the same pgsql user (created as a normal,
non-privileged user with createuser).

When I do:

% for i in $(seq 1 12); do wget -q -O /dev/null http://db1-connecting-website; done

The near-immediate result is:

% ps axfw | grep postgres | grep db1
18231 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55411) idle
18255 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55416) idle
18260 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55420) idle
18263 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55424) idle
18265 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55427) idle
18271 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55432) idle
18273 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55435) idle
18282 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55439) idle
18291 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55443) idle
18317 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55446) idle
18393 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55451) idle
18396 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55455) idle

And N minutes later, the output is identical - they just linger on.

(Database names have been normalized to protect the innocent.)

I tried setting:

statement_timeout = 7000

in postgresql.conf, but it had no effect, the children kept piling on...

 8059 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(48255) idle
 8106 ?        Ss     0:00  \_ postgres: web db2 127.0.0.1(48260) idle
 8119 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48262) idle
 8124 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48265) idle
 8126 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48267) idle
 8341 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(57637) idle
 8357 ?        Ss     0:00  \_ postgres: web db4 127.0.0.1(57640) idle
 8362 ?        Ss     0:00  \_ postgres: web db5 127.0.0.1(57642) idle

When I check how it looks from PostgreSQL's end:

% sudo -u postgres psql template1 -c 'select * from pg_stat_activity;'
 datid  | datname   | procpid | usesysid | usename  | application_name | client_addr | client_port |         backend_start         |          xact_start           |          query_start          | waiting |          current_query
--------+-----------+---------+----------+----------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------------------
 205356 | db1       |    8863 |    16385 | web      |                  | 127.0.0.1   |       57764 | 2011-06-15 11:36:58.126871+02 |                               | 2011-06-15 11:41:47.158675+02 | f       | <IDLE>
 170288 | db2       |    8867 |    16385 | web      |                  | 127.0.0.1   |       57766 | 2011-06-15 11:36:58.227026+02 |                               | 2011-06-15 11:36:58.389238+02 | f       | <IDLE>
 170288 | db2       |    8872 |    16385 | web      |                  | 127.0.0.1   |       57769 | 2011-06-15 11:36:58.409101+02 |                               | 2011-06-15 11:36:58.468774+02 | f       | <IDLE>
 170288 | db2       |    8876 |    16385 | web      |                  | 127.0.0.1   |       57771 | 2011-06-15 11:36:58.491472+02 |                               | 2011-06-15 11:36:58.549619+02 | f       | <IDLE>
 157541 | db3       |    8888 |    16385 | web      |                  | 127.0.0.1   |       57772 | 2011-06-15 11:37:00.809331+02 |                               | 2011-06-15 11:37:00.989411+02 | f       | <IDLE>
 122630 | db4       |    9016 |    16385 | web      |                  | 127.0.0.1   |       57777 | 2011-06-15 11:37:04.802028+02 |                               | 2011-06-15 11:37:05.050344+02 | f       | <IDLE>
 106735 | db5       |    9018 |    16385 | web      |                  | 127.0.0.1   |       57779 | 2011-06-15 11:37:05.08563+02  |                               | 2011-06-15 11:38:48.369282+02 | f       | <IDLE>
 122630 | db4       |    9019 |    16385 | web      |                  | 127.0.0.1   |       57780 | 2011-06-15 11:37:05.096214+02 |                               | 2011-06-15 11:37:05.30666+02  | f       | <IDLE>
 106735 | db5       |    9020 |    16385 | web      |                  | 127.0.0.1   |       57784 | 2011-06-15 11:37:05.293885+02 |                               | 2011-06-15 11:37:05.36558+02  | f       | <IDLE>
 106735 | db5       |    9021 |    16385 | web      |                  | 127.0.0.1   |       57786 | 2011-06-15 11:37:05.414632+02 |                               | 2011-06-15 11:37:05.488409+02 | f       | <IDLE>
 202195 | db6       |    9041 |    16385 | web      |                  | 127.0.0.1   |       57788 | 2011-06-15 11:37:14.966001+02 |                               | 2011-06-15 11:37:28.519769+02 | f       | <IDLE>
  63812 | db7       |    9043 |    16385 | web      |                  | 127.0.0.1   |       57791 | 2011-06-15 11:37:15.276264+02 |                               | 2011-06-15 11:41:50.756364+02 | f       | <IDLE>
 240382 | db8       |    9044 |    16385 | web      |                  | 127.0.0.1   |       57792 | 2011-06-15 11:37:15.665498+02 |                               | 2011-06-15 11:37:16.136575+02 | f       | <IDLE>
 265733 | db9       |    9048 |    16385 | web      |                  | 127.0.0.1   |       57794 | 2011-06-15 11:37:18.065143+02 |                               | 2011-06-15 11:37:18.597728+02 | f       | <IDLE>
 265733 | db9       |    9050 |    16385 | web      |                  | 127.0.0.1   |       57796 | 2011-06-15 11:37:18.294857+02 |                               | 2011-06-15 11:37:18.66658+02  | f       | <IDLE>
 265733 | db9       |    9051 |    16385 | web      |                  | 127.0.0.1   |       57798 | 2011-06-15 11:37:18.401163+02 |                               | 2011-06-15 11:37:21.890123+02 | f       | <IDLE>
 265733 | db9       |    9052 |    16385 | web      |                  | 127.0.0.1   |       57801 | 2011-06-15 11:37:18.506822+02 |                               | 2011-06-15 11:37:18.578728+02 | f       | <IDLE>
 265733 | db9       |    9053 |    16385 | web      |                  | 127.0.0.1   |       57803 | 2011-06-15 11:37:18.560628+02 |                               | 2011-06-15 11:37:18.650382+02 | f       | <IDLE>
 265733 | db9       |    9054 |    16385 | web      |                  | 127.0.0.1   |       57805 | 2011-06-15 11:37:18.610887+02 |                               | 2011-06-15 11:37:48.685439+02 | f       | <IDLE>
 265733 | db9       |    9055 |    16385 | web      |                  | 127.0.0.1   |       57807 | 2011-06-15 11:37:18.674126+02 |                               | 2011-06-15 11:38:16.490799+02 | f       | <IDLE>
 265733 | db9       |    9062 |    16385 | web      |                  | 127.0.0.1   |       57808 | 2011-06-15 11:37:21.72951+02  |                               | 2011-06-15 11:37:49.116284+02 | f       | <IDLE>
 122630 | db4       |    9067 |    16385 | web      |                  | 127.0.0.1   |       57809 | 2011-06-15 11:37:25.26675+02  |                               | 2011-06-15 11:37:25.415022+02 | f       | <IDLE>
 202195 | db6       |    9069 |    16385 | web      |                  | 127.0.0.1   |       57811 | 2011-06-15 11:37:25.721735+02 |                               | 2011-06-15 11:41:16.084124+02 | f       | <IDLE>
[...]
 202195 | db6       |   28584 |    16385 | web      |                  | 127.0.0.1   |       35286 | 2011-06-15 11:41:50.39207+02  |                               | 2011-06-15 11:41:50.605894+02 | f       | <IDLE>
 202195 | db6       |   28583 |    16385 | web      |                  | 127.0.0.1   |       35283 | 2011-06-15 11:41:50.260415+02 |                               | 2011-06-15 11:41:50.448556+02 | f       | <IDLE>
 202195 | db6       |   28585 |    16385 | web      |                  | 127.0.0.1   |       35289 | 2011-06-15 11:41:50.554389+02 |                               | 2011-06-15 11:41:50.771443+02 | f       | <IDLE>
 163920 | db10      |   28588 |    16385 | web      |                  | 127.0.0.1   |       35293 | 2011-06-15 11:41:52.466221+02 |                               | 2011-06-15 11:41:52.600124+02 | f       | <IDLE>
      1 | template1 |   28593 |       10 | postgres | psql             |             |          -1 | 2011-06-15 11:41:53.724276+02 | 2011-06-15 11:41:53.729887+02 | 2011-06-15 11:41:53.729887+02 | f       | select * from pg_stat_activity;
(204 rows)

So the normal queries subsequent to those connections all worked fine,
and then the connections just went into idle mode and never left it.
I know I said I want persistent connections, but not *this* persistent :)

I tried setting:

php_admin_value pgsql.max_persistent 3

in Apache config, and it did come into effect, but it just caused a bunch of
processes to explicitly fail:

[15-Jun-2011 11:34:20] PHP Warning:  pg_pconnect(): Cannot create new link. Too many open persistent links (3) in /srv/app/dbinit.php on line 20

So I'm at a loss what to do to avoid this DoS, right now I'm resorting to
the silly route:

% ps axfw | grep postgres -c
383
% sudo apache2ctl graceful
% ps axfw | grep postgres -c
28

Until next time - in a bit...

Please help! TIA.

-- 
     2. That which causes joy or happiness.

Information forwarded to debian-bugs-dist@lists.debian.org, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package php5-pgsql, postgresql-9.0. (Wed, 15 Jun 2011 10:36:03 GMT) (full text, mbox, link).

Acknowledgement sent to Ondřej Surý <ondrej@sury.org>:
Extra info received and forwarded to list. Copy sent to Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>. (Wed, 15 Jun 2011 10:36:03 GMT) (full text, mbox, link).

Message #10 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Ondřej Surý <ondrej@sury.org>
To: Josip Rodin <joy@debbugs.entuzijast.net>, "630569@bugs.debian.org" <630569@bugs.debian.org>
Subject: Re: [php-maint] Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Wed, 15 Jun 2011 12:32:35 +0200
Hi Josip,

My first question would be if you can repeat same behaviour under unstable (and/or wheezy).

Ondřej Surý

On 15.6.2011, at 12:01, Josip Rodin <joy@debbugs.entuzijast.net> wrote:

> Package: php5-pgsql, postgresql-9.0
> Version: 5.3.3-7+squeeze1, 9.0.4-1~bpo60+1
> Severity: serious
> 
> Hi,
> 
> After the upgrade of one of our web servers to squeeze, coupled with
> PostgreSQL 9 from squeeze-backports, the new pg_pconnect()-caused
> connections seem to get created and left idling forever. When their number
> eventually reaches PostgreSQL's max_connections limit, everything breaks.
> 
> With the lenny PostgreSQL 8.3, the squeeze php5-pgsql seems to be able to
> spool normally, foregoing excess connections over time. But not this one.
> 
> (Obviously I brought this upon myself for trying a new non-Debian-stable
> database version, but the new database version is in wheezy so it matters
> for the next stable anyway.)
> 
> The calling code at this end is literally unchanged for years, it's
> basically a pg_pconnect() call to a set of two hostnames that both resolve
> into 127.0.0.1 via /etc/hosts. All PHP children connect to a variety of
> local databases using the same pgsql user (created as a normal,
> non-privileged user with createuser).
> 
> When I do:
> 
> % for i in $(seq 1 12); do wget -q -O /dev/null http://db1-connecting-website; done
> 
> The near-immediate result is:
> 
> % ps axfw | grep postgres | grep db1
> 18231 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55411) idle
> 18255 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55416) idle
> 18260 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55420) idle
> 18263 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55424) idle
> 18265 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55427) idle
> 18271 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55432) idle
> 18273 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55435) idle
> 18282 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55439) idle
> 18291 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55443) idle
> 18317 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55446) idle
> 18393 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55451) idle
> 18396 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(55455) idle
> 
> And N minutes later, the output is identical - they just linger on.
> 
> (Database names have been normalized to protect the innocent.)
> 
> I tried setting:
> 
> statement_timeout = 7000
> 
> in postgresql.conf, but it had no effect, the children kept piling on...
> 
> 8059 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(48255) idle
> 8106 ?        Ss     0:00  \_ postgres: web db2 127.0.0.1(48260) idle
> 8119 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48262) idle
> 8124 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48265) idle
> 8126 ?        Ss     0:00  \_ postgres: web db3 127.0.0.1(48267) idle
> 8341 ?        Ss     0:00  \_ postgres: web db1 127.0.0.1(57637) idle
> 8357 ?        Ss     0:00  \_ postgres: web db4 127.0.0.1(57640) idle
> 8362 ?        Ss     0:00  \_ postgres: web db5 127.0.0.1(57642) idle
> 
> When I check how it looks from PostgreSQL's end:
> 
> % sudo -u postgres psql template1 -c 'select * from pg_stat_activity;'
> datid  | datname   | procpid | usesysid | usename  | application_name | client_addr | client_port |         backend_start         |          xact_start           |          query_start          | waiting |          current_query
> --------+-----------+---------+----------+----------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------------------
> 205356 | db1       |    8863 |    16385 | web      |                  | 127.0.0.1   |       57764 | 2011-06-15 11:36:58.126871+02 |                               | 2011-06-15 11:41:47.158675+02 | f       | <IDLE>
> 170288 | db2       |    8867 |    16385 | web      |                  | 127.0.0.1   |       57766 | 2011-06-15 11:36:58.227026+02 |                               | 2011-06-15 11:36:58.389238+02 | f       | <IDLE>
> 170288 | db2       |    8872 |    16385 | web      |                  | 127.0.0.1   |       57769 | 2011-06-15 11:36:58.409101+02 |                               | 2011-06-15 11:36:58.468774+02 | f       | <IDLE>
> 170288 | db2       |    8876 |    16385 | web      |                  | 127.0.0.1   |       57771 | 2011-06-15 11:36:58.491472+02 |                               | 2011-06-15 11:36:58.549619+02 | f       | <IDLE>
> 157541 | db3       |    8888 |    16385 | web      |                  | 127.0.0.1   |       57772 | 2011-06-15 11:37:00.809331+02 |                               | 2011-06-15 11:37:00.989411+02 | f       | <IDLE>
> 122630 | db4       |    9016 |    16385 | web      |                  | 127.0.0.1   |       57777 | 2011-06-15 11:37:04.802028+02 |                               | 2011-06-15 11:37:05.050344+02 | f       | <IDLE>
> 106735 | db5       |    9018 |    16385 | web      |                  | 127.0.0.1   |       57779 | 2011-06-15 11:37:05.08563+02  |                               | 2011-06-15 11:38:48.369282+02 | f       | <IDLE>
> 122630 | db4       |    9019 |    16385 | web      |                  | 127.0.0.1   |       57780 | 2011-06-15 11:37:05.096214+02 |                               | 2011-06-15 11:37:05.30666+02  | f       | <IDLE>
> 106735 | db5       |    9020 |    16385 | web      |                  | 127.0.0.1   |       57784 | 2011-06-15 11:37:05.293885+02 |                               | 2011-06-15 11:37:05.36558+02  | f       | <IDLE>
> 106735 | db5       |    9021 |    16385 | web      |                  | 127.0.0.1   |       57786 | 2011-06-15 11:37:05.414632+02 |                               | 2011-06-15 11:37:05.488409+02 | f       | <IDLE>
> 202195 | db6       |    9041 |    16385 | web      |                  | 127.0.0.1   |       57788 | 2011-06-15 11:37:14.966001+02 |                               | 2011-06-15 11:37:28.519769+02 | f       | <IDLE>
>  63812 | db7       |    9043 |    16385 | web      |                  | 127.0.0.1   |       57791 | 2011-06-15 11:37:15.276264+02 |                               | 2011-06-15 11:41:50.756364+02 | f       | <IDLE>
> 240382 | db8       |    9044 |    16385 | web      |                  | 127.0.0.1   |       57792 | 2011-06-15 11:37:15.665498+02 |                               | 2011-06-15 11:37:16.136575+02 | f       | <IDLE>
> 265733 | db9       |    9048 |    16385 | web      |                  | 127.0.0.1   |       57794 | 2011-06-15 11:37:18.065143+02 |                               | 2011-06-15 11:37:18.597728+02 | f       | <IDLE>
> 265733 | db9       |    9050 |    16385 | web      |                  | 127.0.0.1   |       57796 | 2011-06-15 11:37:18.294857+02 |                               | 2011-06-15 11:37:18.66658+02  | f       | <IDLE>
> 265733 | db9       |    9051 |    16385 | web      |                  | 127.0.0.1   |       57798 | 2011-06-15 11:37:18.401163+02 |                               | 2011-06-15 11:37:21.890123+02 | f       | <IDLE>
> 265733 | db9       |    9052 |    16385 | web      |                  | 127.0.0.1   |       57801 | 2011-06-15 11:37:18.506822+02 |                               | 2011-06-15 11:37:18.578728+02 | f       | <IDLE>
> 265733 | db9       |    9053 |    16385 | web      |                  | 127.0.0.1   |       57803 | 2011-06-15 11:37:18.560628+02 |                               | 2011-06-15 11:37:18.650382+02 | f       | <IDLE>
> 265733 | db9       |    9054 |    16385 | web      |                  | 127.0.0.1   |       57805 | 2011-06-15 11:37:18.610887+02 |                               | 2011-06-15 11:37:48.685439+02 | f       | <IDLE>
> 265733 | db9       |    9055 |    16385 | web      |                  | 127.0.0.1   |       57807 | 2011-06-15 11:37:18.674126+02 |                               | 2011-06-15 11:38:16.490799+02 | f       | <IDLE>
> 265733 | db9       |    9062 |    16385 | web      |                  | 127.0.0.1   |       57808 | 2011-06-15 11:37:21.72951+02  |                               | 2011-06-15 11:37:49.116284+02 | f       | <IDLE>
> 122630 | db4       |    9067 |    16385 | web      |                  | 127.0.0.1   |       57809 | 2011-06-15 11:37:25.26675+02  |                               | 2011-06-15 11:37:25.415022+02 | f       | <IDLE>
> 202195 | db6       |    9069 |    16385 | web      |                  | 127.0.0.1   |       57811 | 2011-06-15 11:37:25.721735+02 |                               | 2011-06-15 11:41:16.084124+02 | f       | <IDLE>
> [...]
> 202195 | db6       |   28584 |    16385 | web      |                  | 127.0.0.1   |       35286 | 2011-06-15 11:41:50.39207+02  |                               | 2011-06-15 11:41:50.605894+02 | f       | <IDLE>
> 202195 | db6       |   28583 |    16385 | web      |                  | 127.0.0.1   |       35283 | 2011-06-15 11:41:50.260415+02 |                               | 2011-06-15 11:41:50.448556+02 | f       | <IDLE>
> 202195 | db6       |   28585 |    16385 | web      |                  | 127.0.0.1   |       35289 | 2011-06-15 11:41:50.554389+02 |                               | 2011-06-15 11:41:50.771443+02 | f       | <IDLE>
> 163920 | db10      |   28588 |    16385 | web      |                  | 127.0.0.1   |       35293 | 2011-06-15 11:41:52.466221+02 |                               | 2011-06-15 11:41:52.600124+02 | f       | <IDLE>
>      1 | template1 |   28593 |       10 | postgres | psql             |             |          -1 | 2011-06-15 11:41:53.724276+02 | 2011-06-15 11:41:53.729887+02 | 2011-06-15 11:41:53.729887+02 | f       | select * from pg_stat_activity;
> (204 rows)
> 
> So the normal queries subsequent to those connections all worked fine,
> and then the connections just went into idle mode and never left it.
> I know I said I want persistent connections, but not *this* persistent :)
> 
> I tried setting:
> 
> php_admin_value pgsql.max_persistent 3
> 
> in Apache config, and it did come into effect, but it just caused a bunch of
> processes to explicitly fail:
> 
> [15-Jun-2011 11:34:20] PHP Warning:  pg_pconnect(): Cannot create new link. Too many open persistent links (3) in /srv/app/dbinit.php on line 20
> 
> So I'm at a loss what to do to avoid this DoS, right now I'm resorting to
> the silly route:
> 
> % ps axfw | grep postgres -c
> 383
> % sudo apache2ctl graceful
> % ps axfw | grep postgres -c
> 28
> 
> Until next time - in a bit...
> 
> Please help! TIA.
> 
> -- 
>     2. That which causes joy or happiness.
> 
> 
> 
> _______________________________________________
> pkg-php-maint mailing list
> pkg-php-maint@lists.alioth.debian.org
> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-php-maint

Information forwarded to debian-bugs-dist@lists.debian.org, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package php5-pgsql, postgresql-9.0. (Wed, 15 Jun 2011 10:36:05 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Extra info received and forwarded to list. Copy sent to Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>. (Wed, 15 Jun 2011 10:36:05 GMT) (full text, mbox, link).

Message #15 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: 630569@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Wed, 15 Jun 2011 12:34:33 +0200
On Wed, Jun 15, 2011 at 12:32:35PM +0200, Ondřej Surý wrote:
> My first question would be if you can repeat same behaviour under unstable (and/or wheezy).

I'll try and get back to you on that. (It'll take me a while to replicate
a machine and then upgrade it.)

-- 
     2. That which causes joy or happiness.

Information forwarded to debian-bugs-dist@lists.debian.org, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package php5-pgsql, postgresql-9.0. (Wed, 15 Jun 2011 20:27:03 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Extra info received and forwarded to list. Copy sent to Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>, Martin Pitt <mpitt@debian.org>. (Wed, 15 Jun 2011 20:27:03 GMT) (full text, mbox, link).

Message #20 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: 630569@bugs.debian.org
Cc: control@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Wed, 15 Jun 2011 22:25:16 +0200
reassign 630569 postgresql-8.4, postgresql-9.0, php5-pgsql
thanks

On Wed, Jun 15, 2011 at 12:32:35PM +0200, Ondřej Surý wrote:
> My first question would be if you can repeat same behaviour under unstable
> (and/or wheezy).

Before going forward to wheeze, I first tried to reproduce it on clean
squeeze because this is affecting my production machines... Unfortunately,
I'm seeing the same problem with PostgreSQL 8.4 which is in stable. :(

As I mentioned earlier, this is what happens on a separate machine that has
been largely upgraded to squeeze, but left with the lenny pgsql server:

% dpkg -s libapache2-mod-php5 php5-pgsql postgresql-8.3 | grep Version
Version: 5.3.3-7+squeeze1
Version: 5.3.3-7+squeeze1
Version: 8.3.14-0lenny1
% for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
7
7
7
7
7
7
7
7
7
7
7
7
% for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
7
7
7
7
7
7
7
7
7
7
7
7

So, the spooling works normally and no excess connections are being
left behind.

Whereas on a squeeze machine:

% dpkg -s libapache2-mod-php5 php5-pgsql postgresql-8.4 | grep Version
Version: 5.3.3-7+squeeze1
Version: 5.3.3-7+squeeze1
Version: 8.4.7-0squeeze2
% sudo apache2ctl graceful
% ps -C postgres | tail -n +2 | wc -l
5
% for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
6
7
8
9
10
11
12
13
14
15
16
17
% for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
18
19
20
21
22
23
24
24
24
24
24
24
% for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
24
24
24
24
24
24
24
24
24
25
25
25

After that it levels off. And, just to confirm:

% sudo -u postgres psql template1 -c 'select datname,usename,current_query from pg_stat_activity;' | sort | uniq -c | sort -n
      1
      1   datname   | usename  |                        current_query
      1  template1  | postgres | select datname,usename,current_query from pg_stat_activity;
      1 (21 rows)
      1 ------------+----------+-------------------------------------------------------------
     20  db1        | web      | <IDLE>

-- 
     2. That which causes joy or happiness.

Bug reassigned from package 'php5-pgsql, postgresql-9.0' to 'postgresql-8.4,postgresql-9.0,php5-pgsql'. Request was from Josip Rodin <joy@debbugs.entuzijast.net> to control@bugs.debian.org. (Wed, 15 Jun 2011 20:27:07 GMT) (full text, mbox, link).

Changed Bug title to 'php5-pgsql and postgresql >= 8.4 seem to collude never to close idle persistent connections' from 'php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections' Request was from Josip Rodin <joy@debbugs.entuzijast.net> to control@bugs.debian.org. (Wed, 15 Jun 2011 20:45:02 GMT) (full text, mbox, link).

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0,php5-pgsql. (Fri, 17 Jun 2011 14:33:03 GMT) (full text, mbox, link).

Acknowledgement sent to Ondřej Surý <ondrej@debian.org>:
Extra info received and forwarded to list. Copy sent to Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>. (Fri, 17 Jun 2011 14:33:03 GMT) (full text, mbox, link).

Message #29 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Ondřej Surý <ondrej@debian.org>
To: Josip Rodin <joy@debbugs.entuzijast.net>, 630569@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Fri, 17 Jun 2011 16:28:14 +0200
I still don't see how this could be a php5-pgsql bug since it's caused
by pgsql version.

But still I have one more another important question:

Is this related to libpq5 version or postgresql server version?

O.

2011/6/15 Josip Rodin <joy@debbugs.entuzijast.net>:
> reassign 630569 postgresql-8.4, postgresql-9.0, php5-pgsql
> thanks
>
> On Wed, Jun 15, 2011 at 12:32:35PM +0200, Ondřej Surý wrote:
>> My first question would be if you can repeat same behaviour under unstable
>> (and/or wheezy).
>
> Before going forward to wheeze, I first tried to reproduce it on clean
> squeeze because this is affecting my production machines... Unfortunately,
> I'm seeing the same problem with PostgreSQL 8.4 which is in stable. :(
>
> As I mentioned earlier, this is what happens on a separate machine that has
> been largely upgraded to squeeze, but left with the lenny pgsql server:
>
> % dpkg -s libapache2-mod-php5 php5-pgsql postgresql-8.3 | grep Version
> Version: 5.3.3-7+squeeze1
> Version: 5.3.3-7+squeeze1
> Version: 8.3.14-0lenny1
> % for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> % for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
> 7
>
> So, the spooling works normally and no excess connections are being
> left behind.
>
> Whereas on a squeeze machine:
>
> % dpkg -s libapache2-mod-php5 php5-pgsql postgresql-8.4 | grep Version
> Version: 5.3.3-7+squeeze1
> Version: 5.3.3-7+squeeze1
> Version: 8.4.7-0squeeze2
> % sudo apache2ctl graceful
> % ps -C postgres | tail -n +2 | wc -l
> 5
> % for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
> 6
> 7
> 8
> 9
> 10
> 11
> 12
> 13
> 14
> 15
> 16
> 17
> % for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
> 18
> 19
> 20
> 21
> 22
> 23
> 24
> 24
> 24
> 24
> 24
> 24
> % for i in $(seq 1 12); do wget -q -O /dev/null website-using-local-pgsql; ps -C postgres | tail -n +2 | wc -l; done
> 24
> 24
> 24
> 24
> 24
> 24
> 24
> 24
> 24
> 25
> 25
> 25
>
> After that it levels off. And, just to confirm:
>
> % sudo -u postgres psql template1 -c 'select datname,usename,current_query from pg_stat_activity;' | sort | uniq -c | sort -n
>      1
>      1   datname   | usename  |                        current_query
>      1  template1  | postgres | select datname,usename,current_query from pg_stat_activity;
>      1 (21 rows)
>      1 ------------+----------+-------------------------------------------------------------
>     20  db1        | web      | <IDLE>
>
> --
>     2. That which causes joy or happiness.
>
>
>
> _______________________________________________
> pkg-php-maint mailing list
> pkg-php-maint@lists.alioth.debian.org
> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-php-maint
>



-- 
Ondřej Surý <ondrej@sury.org>
http://blog.rfc1925.org/

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0,php5-pgsql. (Fri, 17 Jun 2011 18:21:03 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Extra info received and forwarded to list. Copy sent to Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>. (Fri, 17 Jun 2011 18:21:03 GMT) (full text, mbox, link).

Message #34 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: Ondřej Surý <ondrej@debian.org>
Cc: 630569@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Fri, 17 Jun 2011 20:17:00 +0200
On Fri, Jun 17, 2011 at 04:28:14PM +0200, Ondřej Surý wrote:
> I still don't see how this could be a php5-pgsql bug since it's caused
> by pgsql version.

I filed it preemptively on all so as to solicit help... if there is no odd
variable behavior in php5-pgsql based on server version, please feel free to
reassign away.

> But still I have one more another important question:
> 
> Is this related to libpq5 version or postgresql server version?

I did not test downgrading libpq5, just postgresql-*, good point, that'll
be my next step. After vacation...

-- 
     2. That which causes joy or happiness.

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0,php5-pgsql. (Sat, 18 Jun 2011 11:27:10 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Extra info received and forwarded to list. Copy sent to Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>. (Sat, 18 Jun 2011 11:27:21 GMT) (full text, mbox, link).

Message #39 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: 630569@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Sat, 18 Jun 2011 13:22:28 +0200
On Fri, Jun 17, 2011 at 08:17:00PM +0200, Josip Rodin wrote:
> On Fri, Jun 17, 2011 at 04:28:14PM +0200, Ondřej Surý wrote:
> > I still don't see how this could be a php5-pgsql bug since it's caused
> > by pgsql version.
> 
> I filed it preemptively on all so as to solicit help... if there is no odd
> variable behavior in php5-pgsql based on server version, please feel free to
> reassign away.
> 
> > But still I have one more another important question:
> > 
> > Is this related to libpq5 version or postgresql server version?
> 
> I did not test downgrading libpq5, just postgresql-*, good point, that'll
> be my next step. After vacation...

I couldn't resist, and it seemed like an easy test. I manually downgraded
libpq5 to 8.4.7-0squeeze2, restarted both PostgreSQL and Apache (mod_php),
and the test result was the same.

-- 
     2. That which causes joy or happiness.

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0,php5-pgsql. (Tue, 23 Aug 2011 21:23:14 GMT) (full text, mbox, link).

Acknowledgement sent to Ondřej Surý <ondrej@debian.org>:
Extra info received and forwarded to list. Copy sent to Martin Pitt <mpitt@debian.org>, Debian PHP Maintainers <pkg-php-maint@lists.alioth.debian.org>. (Tue, 23 Aug 2011 21:23:14 GMT) (full text, mbox, link).

Message #44 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Ondřej Surý <ondrej@debian.org>
To: Josip Rodin <joy@debbugs.entuzijast.net>
Cc: 630569@bugs.debian.org
Subject: Re: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Tue, 23 Aug 2011 23:21:38 +0200
reassign 630569 postgresql-8.4, postgresql-9.0
thank

Hi,

2011/6/17 Josip Rodin <joy@debbugs.entuzijast.net>:
> On Fri, Jun 17, 2011 at 04:28:14PM +0200, Ondřej Surý wrote:
>> I still don't see how this could be a php5-pgsql bug since it's caused
>> by pgsql version.
>
> I filed it preemptively on all so as to solicit help... if there is no odd
> variable behavior in php5-pgsql based on server version, please feel free to
> reassign away.

I am reassigning the bug just to postgresql packages since it happens when
the server is upgraded (so it might be a regression in the postgresql code).
If the postgresql maintainer disagree with me, he can reassign the bug back :).

O.
-- 
Ondřej Surý <ondrej@sury.org>
http://blog.rfc1925.org/

Bug reassigned from package 'postgresql-8.4,postgresql-9.0,php5-pgsql' to 'postgresql-8.4,postgresql-9.0'. Request was from Ondřej Surý <ondrej@debian.org> to control@bugs.debian.org. (Tue, 23 Aug 2011 21:23:20 GMT) (full text, mbox, link).

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0. (Sat, 15 Oct 2011 17:48:06 GMT) (full text, mbox, link).

Message #49 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Christoph Berg <myon@debian.org>
To: Josip Rodin <joy@debbugs.entuzijast.net>, 630569@bugs.debian.org
Subject: Re: Bug#630569: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Sat, 15 Oct 2011 19:44:29 +0200
[Message part 1 (text/plain, inline)]
tags 630569 moreinfo
severity 630569 important
thanks

Re: Josip Rodin 2011-06-18 <20110618112228.GA15373@entuzijast.net>
> > > I still don't see how this could be a php5-pgsql bug since it's caused
> > > by pgsql version.
> > 
> > I filed it preemptively on all so as to solicit help... if there is no odd
> > variable behavior in php5-pgsql based on server version, please feel free to
> > reassign away.
> > 
> > > But still I have one more another important question:
> > > 
> > > Is this related to libpq5 version or postgresql server version?
> > 
> > I did not test downgrading libpq5, just postgresql-*, good point, that'll
> > be my next step. After vacation...
> 
> I couldn't resist, and it seemed like an easy test. I manually downgraded
> libpq5 to 8.4.7-0squeeze2, restarted both PostgreSQL and Apache (mod_php),
> and the test result was the same.

Hi Joy,

I doubt that this is a postgresql server bug (no one else has hit it
yet). As the server never closes connections by itself, it must be on
the client side, either in libpq5 (which I doubt), or (more likely) in
some connection caching layer which you mentioned earlier. Did you
also upgrade apache and/or moved to some other MPM? What about
mod_php?

Christoph
-- 
cb@df7cb.de | http://www.df7cb.de/

[signature.asc (application/pgp-signature, inline)]

Added tag(s) moreinfo. Request was from Christoph Berg <myon@debian.org> to control@bugs.debian.org. (Sat, 15 Oct 2011 17:48:08 GMT) (full text, mbox, link).

Severity set to 'important' from 'serious' Request was from Christoph Berg <myon@debian.org> to control@bugs.debian.org. (Sat, 15 Oct 2011 17:48:08 GMT) (full text, mbox, link).

Information forwarded to debian-bugs-dist@lists.debian.org, Martin Pitt <mpitt@debian.org>:
Bug#630569; Package postgresql-8.4,postgresql-9.0. (Mon, 17 Oct 2011 09:09:19 GMT) (full text, mbox, link).

Acknowledgement sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Extra info received and forwarded to list. Copy sent to Martin Pitt <mpitt@debian.org>. (Mon, 17 Oct 2011 09:09:24 GMT) (full text, mbox, link).

Message #58 received at 630569@bugs.debian.org (full text, mbox, reply):

From: Josip Rodin <joy@debbugs.entuzijast.net>
To: Christoph Berg <myon@debian.org>, 630569@bugs.debian.org
Subject: Re: Bug#630569: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Mon, 17 Oct 2011 11:07:35 +0200
On Sat, Oct 15, 2011 at 07:44:29PM +0200, Christoph Berg wrote:
> > > > I still don't see how this could be a php5-pgsql bug since it's caused
> > > > by pgsql version.
> > > 
> > > I filed it preemptively on all so as to solicit help... if there is no odd
> > > variable behavior in php5-pgsql based on server version, please feel free to
> > > reassign away.
> > > 
> > > > But still I have one more another important question:
> > > > 
> > > > Is this related to libpq5 version or postgresql server version?
> > > 
> > > I did not test downgrading libpq5, just postgresql-*, good point, that'll
> > > be my next step. After vacation...
> > 
> > I couldn't resist, and it seemed like an easy test. I manually downgraded
> > libpq5 to 8.4.7-0squeeze2, restarted both PostgreSQL and Apache (mod_php),
> > and the test result was the same.
> 
> Hi Joy,
> 
> I doubt that this is a postgresql server bug (no one else has hit it
> yet). As the server never closes connections by itself, it must be on
> the client side, either in libpq5 (which I doubt), or (more likely) in
> some connection caching layer which you mentioned earlier. Did you
> also upgrade apache and/or moved to some other MPM? What about
> mod_php?

I didn't change the MPM but I did upgrade the entire web server, from lenny
to squeeze, as I said in the first mail :) so yes, the underlying Apache
and mod_php were upgraded the same way as well.

In retrospect, it's almost as if it was actually a failure in lenny's
software to observe the persist option, and squeeze actually does the
persistence (and I don't actually want it because it kills the use case).

-- 
     2. That which causes joy or happiness.

Reply sent to Christoph Berg <myon@debian.org>:
You have taken responsibility. (Mon, 30 Apr 2012 11:45:15 GMT) (full text, mbox, link).

Notification sent to Josip Rodin <joy@debbugs.entuzijast.net>:
Bug acknowledged by developer. (Mon, 30 Apr 2012 11:45:24 GMT) (full text, mbox, link).

Message #63 received at 630569-done@bugs.debian.org (full text, mbox, reply):

From: Christoph Berg <myon@debian.org>
To: Josip Rodin <joy@debbugs.entuzijast.net>, 630569-done@bugs.debian.org
Subject: Re: Bug#630569: [php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Date: Mon, 30 Apr 2012 13:40:00 +0200
[Message part 1 (text/plain, inline)]
Re: Josip Rodin 2011-10-17 <20111017090735.GA29839@entuzijast.net>
> I didn't change the MPM but I did upgrade the entire web server, from lenny
> to squeeze, as I said in the first mail :) so yes, the underlying Apache
> and mod_php were upgraded the same way as well.
> 
> In retrospect, it's almost as if it was actually a failure in lenny's
> software to observe the persist option, and squeeze actually does the
> persistence (and I don't actually want it because it kills the use case).

I guess it is too late now to actually debug this. I still doubt it
was a postgresql bug, so I'm closing it now - more likely there was an
"interesting" change in the persistence layer as you said.

Christoph
-- 
cb@df7cb.de | http://www.df7cb.de/

[signature.asc (application/pgp-signature, inline)]

Bug archived. Request was from Debbugs Internal Request <owner@bugs.debian.org> to internal_control@bugs.debian.org. (Tue, 29 May 2012 07:41:23 GMT) (full text, mbox, link).

Send a report that this bug log contains spam.

Debian bug tracking system administrator <owner@bugs.debian.org>. Last modified: Sun Jul 2 02:32:25 2023; Machine Name: bembo

Debian Bug tracking system

Debbugs is free software and licensed under the terms of the GNU Public License version 2. The current version can be obtained from https://bugs.debian.org/debbugs-source/.

Copyright © 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson, 2005-2017 Don Armstrong, and many other contributors.