npgsql Leaking Postgres DB Connections: Way to monitor connections?

Question

Background: I'm moving my application from npgsql v1 to npgsql v2.0.9. After a few minutes of running my application, I get a System.Exception: Timeout while getting a connection from the pool.

The web claims that this is due to leaking connections (opening a db connection, but not properly closing them).

So

I'm trying to diagnose leaking postgres connections in npgsql.

From the various web literature around; one way to diagnose leaking connections is to setup logging on npgsql, and look for the leaking connection warning message in the log. Problem is, I'm not seeing this message in the logs anywhere.

I also found utility that monitors npgsql connections, but it's unstable and crashes.

So I'm left manually inspecting code. For everyplace that creates an npgsql connection, there is a finally block disposing of it. For everyplace that opens a datareader, CommandBehavior.CloseConnection is used (and the datareader is disposed).

Any other places to check or can someone recommend a way to look for leaking pool connections?

Dangerous

I'm having the exact same problem. But I find connections only leak when I use DbConnection.BeginTransaction and DbTransaction.Commit/Rollback. I'm making sure I close/dispose all connections properly as well but no help. I think it's a problem with Npgsql itself and not our code. Do let me know if you've found the solution. Thanks. – Empennage

Answer 1

UPDATE: I'm working in a better way to check where those leak connections were allocated. Please, check my post about that: http://fxjr.blogspot.com/2012/11/better-tracing-of-npgsql-connection.html I hope it helps.

EDIT: You may want to try our latest beta version 2.0.11.91. We added some more code to improve connection pool which can help you.

Hmmmm, this is very strange.... You should be getting some messages.

Those messages only appear when ms.net runtime finalizes the object though. Would it be possible that you are using more than default max connections at same time? I mean, your peak usage may be more than the 20 max number connections...

I hope it helps.

Please, let me know what do you get.

Dumyat

Even if I crank up the connectons to 100, I still end up with this exception. I'm pretty positive we're not using that many connections. – Dangerous

Can you try disable connection pooling and check if you see connections floating in the server? You can disable pooling by adding Pooling=false in your connection string. If after using your app for a while you see connections in your pg server, you may be leaking connections. I hope it helps. – Dumyat

Hi Francisco, Thanks for your help. I disabled pooling using Pooling=false as you suggested. In order to monitor connections I am using SELECT * FROM pg_stat_activity SQL query. The most connections I saw was 11, then it went back down to 1 (for the sql query) bouncing up to 3 or 4 and back down. As an aside, disabling pooling really slows down performance of npgsql/postgres. – Dangerous

Sorry for delay... This is really very strange that you are getting timeout from the pool. I have no idea about what can be happening in order to give you this error since after disabling connection pooling it seems to be closing the connections correctly. Do you think you can write a test sample where this problem appears? It would be very much helpful. Thanks in advance. – Dumyat

Hint: If you switch off pooling in the connectionstring then it works pretty good for me. – Punchdrunk

Still have these timeouts even with latest 2.0.13 beta 1. Will disable pooling for now. – Connaught

I'm finding lots of hanging connections with "UNLISTEN *" as their query. What could that be about? – Halla

Is this possible in Npgsql v5? I am not seeing any performance counters available. – Toluidine