Re: Problem with leaking connections

On Thu, 2009-09-03 at 17:55 +0200, Mark Lange wrote:
> Hi,

> The pool is configured to aggressively close connections when it is
> exhausted, but the application gets very slow when this happens,
> mostly we have to restart
>
> the tomcat server.
>
> #ps –aux | grep postgres on the database host shows me many open
> connections in the select or in transaction state (that never get
> closed until tomcat or postgres restart).
>
> Is there a way to figure out which statement didn’t closed the
> connections?
>
It isn't a statement it is your app code. If you have a ps aux that is
showing select it means you have selects running that aren't finished.
If it shows in transaction state (I assume idle in transaction) it also
means your app code is not properly committing or rollingback
transactions.

> Is it possible to configure postgres to close connections after a
> timeout or something like this (maybe statement_timeout)?
>

statement_timeout will terminate a query not a connection.

> Are there any other possibilities?
>

Fix your code. Not to sound harsh but this is blatant code issues.

Joshua D. Drake

>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering