Re: Re: re : PHP and persistent connections

GH <grasshacker(at)over-yonder(dot)net> writes:
> Do the "persistent-connected" Postgres backends ever timeout or die?

No. A backend will sit patiently for the client to send it another
query or close the connection.

(Barely on topic: in recent releases, the backend does set TCP
"keepalive" mode on the client socket. On a cross-machine connection,
this causes the kernel to ping every so often on an idle connection, to
make sure that the peer machine is still alive and still believes the
connection is open. However, this does not guard against a client
process that is holding connections open without any intention of using
them again soon --- it only protects against half-open connections left
over after a system crash at the client end. In any case, I believe the
total time delay before declaring the connection lost has to be an hour
or more in a spec-compliant TCP implementation.)

> Is it possible to set something like a timeout for persistent connctions?
> (Er, would that be something that someone would want
> to do? A Bad Thing?)

This has been suggested before, but I don't think any of the core
developers consider it a good idea. Having the backend arbitrarily
disconnect on an active client would be a Bad Thing for sure. Hence,
any workable timeout would have to be quite large (order of an
hour, maybe? not milliseconds anyway). And that means that it's not
an effective solution for the problem. Under load, a webserver that
wastes backend connections will run out of available backends long
before a safe timeout would start to clean up after it.

To my mind, a client app that wants to use persistent connections
has got to implement some form of connection pooling, so that it
recycles idle connections back to a "pool" for allocation to task
threads that want to make a new query. And the threads have to release
connections back to the pool as soon as they're done with a transaction.
Actively releasing an idle connection is essential, rather than
depending on a timeout.

I haven't studied PHP at all, but from this conversation I gather that
it's only halfway there...

regards, tom lane