Hello,
I have a tomcat server with 7 applications connecting to PG 8.4. All apps use connection pooling. Over the weekend the apps ran out of connections. The max connections is at the default of 100 which is more than enough for normal operations. In attempting to resolve the situation I shutdown tomcat only to find that PG still appeared to have connections out. So if a connection is between A and B, the A side (tomcat) was shut down, but the B side still thinks it's connected. I had to restart PG to get the connections to clean up.
I've had a similar situation happen where the database server was severely overloaded. Many long-running queries were started. The application connection pools closed the connections after they had been out too long (60 seconds). Eventually things caught up where CPU usage of the database server returned to normal but there remained several backends that did not appear to be connected anymore (a "select from pg_stat_activity" query in combination with "top" showed this). The situation this weekend does not appear to have been triggered by a heavy load.
My question is this: how is it possible for PG to get into a state where it thinks it is connected to an application but the application doesn't agree? (Since I was unable to connect due to max clients reached, I was not able to kill the backends using psql). I'm looking for any avenue that I might explore.
Thanks.