Impulsive
.NET 8.0, PostgreSql and left over Idle sessions
I have a .NET 8 webapp and we've done a dumb and manually handled postgres database connections throughout our classes.. I think this could be the root of my problems really... We new up connections and pass the connection around through our various classes, we end up sometimes passing the same connection down 2,3,4 times, re-using it, with the final disposal being quite some time later... making it hard to follow where idle connections are even coming from. One clue might be that the Idle connections don't even have a query associated with them... (making it really hard to pinpoint where they are coming from).
I still feel like a noob C# developer (about 2 yr) and haven't touched things like dependency injection yet.. does anyone here know of any good resources for setting up and managing postgres DB connections that get implemented across many classes?
My issues is we have leaky connections, even though i've been careful to go through the entire project and wrap ever instance of NpgsqlConnection/NpgsqlCommand in using statements and (tried to) implement the IDisposable interface for each class using the postgres connections. But i'm still finding some leaky connections/sessions building up over time.
Initially I had an idle connection for every click on the front end... now I only get one every few minutes~.
If anyone has any suggestion/tips i'd be hugely greatful!
P.S. it could be that i just need to bite the bullet and convert to a singleton and add Transients for each class using the database.. thoughts? then go through every implementation of the DBConnection and just swap it out to the singleton instance hopefully to have better control/single point of connection creation.
I am not looking for code implementatin specifically just a good idea of what the best way to maybe approach this issue. if you need any extra detail let me know i'll give us much as i can.
19 replies