PostgreSQL and pconnect() ... an evil combination ...

[The Hermit Hacker]

In phplib/db_pgsql.inc, the connect is called as:

  function connect() {
          if ( 0 == $this->Link_ID ) {
                  $cstr = "dbname=".$this->Database.
                  $this->ifadd($this->Host, "host=").
                  $this->ifadd($this->Port, "port=").
                  $this->ifadd($this->User, "user=").
                  $this->ifadd($this->Password, "password=");
                  $this->Link_ID=pg_pconnect($cstr);
                  if (!$this->Link_ID) {
                          $this->halt("Link-ID == false, pconnect failed");
                  }
          }
  }

It is well known that PHP and persistent connections to PostgreSQL are
broken, such that you end up with quite a few idle processes in your
backend that will never get re-used:

pgsql   814  0.0  1.1  9384 5632  ??  I    12:20PM   0:00.15 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql   822  0.0  1.1  9384 5484  ??  I    12:20PM   0:00.12 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1079  0.0  1.1  9384 5584  ??  I    12:22PM   0:00.13 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1082  0.0  1.1  9384 5580  ??  I    12:22PM   0:00.13 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1083  0.0  1.1  9384 5520  ??  I    12:22PM   0:00.12 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1084  0.0  1.1  9384 5588  ??  I    12:22PM   0:00.13 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1102  0.0  0.2  1388 1000  p0  S    12:22PM   0:00.12 -su (tcsh)
pgsql  1173  0.0  0.9  9396 4712  ??  I    12:23PM   0:00.06 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1227  0.0  0.9  9388 4796  ??  I    12:23PM   0:00.07 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1295  0.0  1.1  9388 5564  ??  I    12:24PM   0:00.11 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1322  0.0  1.1  9388 5572  ??  I    12:24PM   0:00.12 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1333  0.0  1.0  9388 5420  ??  I    12:24PM   0:00.10 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1337  0.0  1.1  9388 5524  ??  I    12:24PM   0:00.10 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1361  0.0  1.0  9388 5132  ??  I    12:24PM   0:00.08 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1362  0.0  1.0  9420 5432  ??  I    12:24PM   0:00.09 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1372  0.0  1.1  9388 5516  ??  I    12:24PM   0:00.10 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1376  0.0  1.0  9388 5276  ??  I    12:24PM   0:00.08 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1394  0.0  1.0  9420 5468  ??  I    12:24PM   0:00.10 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)
pgsql  1408  0.0  0.9  9388 4664  ??  I    12:24PM   0:00.06 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1413  0.0  1.0  9388 5360  ??  I    12:24PM   0:00.07 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.28 horde idle (postgres)
pgsql  1951  0.0  1.0  9388 5304  ??  I    12:29PM   0:00.07 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.128 horde idle (postgres)
pgsql  1961  0.0  0.9  9388 4776  ??  I    12:29PM   0:00.06 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.128 horde idle (postgres)
pgsql  1963  0.0  0.9  9388 4680  ??  I    12:29PM   0:00.05 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.128 horde idle (postgres)
pgsql  2097  0.0  1.1  9388 5516  ??  I    12:30PM   0:00.07 postmaster: /pgsql/bin/postgres hordemgr 216.126.85.73 horde idle (postgres)

Changing pg_pconnect back to just pg_connect fixes the problem ... in
fact, it appears to also fix the problem I reported earlier about trapping
error messages ... 


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org