[php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections

[Josip Rodin]

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.