5

I started out with this - SemaphoreSlim to protect the connection pool from exhaustion, and quickly realized that my problem is that connections are not returned to the pool when I dispose of the DbContext.

Consider that all connections to database is done within a using statement, as such:

using (var context = ContextFactory.GetContext<PostgreSqlDatabaseContext>(connectionString))
{
    var query = $"SELECT * FROM public.\"MyTable\" WHERE \"MyId\" = '{id}'";
    var result = await context.MyTable.FromSql(query).SingleOrDefaultAsync();
    return result;
}

The ContextFactory looks like this:

internal class ContextFactory
{
    public static T GetContext<T>(string sqlConnection) where T : DbContext
    {
        var optionsBuilder = new DbContextOptionsBuilder<PostgreSqlDatabaseContext>();
        optionsBuilder.UseNpgsql(sqlConnection);
        return new PostgreSqlDatabaseContext(optionsBuilder.Options) as T;
    }
}

Regardless of what I do with the connection string, setting Enlist=true as suggested here, or throttling connections using SemaphoreSlim as suggested here I get the same result:

The connection pool has been exhausted, either raise MaxPoolSize (currently 20) or Timeout (currently 15 seconds)

Does Npgsql work at all? What is going on here?

Note that I have read this and this and they are not the same.

Marcus
  • 8,230
  • 11
  • 61
  • 88
  • The connections *are* returned to the pool when you dispose the context, unless you open them separately and keep them open. Create a *minimal* example that reproduces the behaviour, without any factories, custom code or even raw SQL queries. If you want to test Npgsql itself, use plain ADO.NET classes, not EF. I suspect in both cases you'll find there are no leaks. If that happens you'll know it's the rest of the code code that keeps stuff open when it shouldn't – Panagiotis Kanavos Mar 21 '18 at 08:43
  • BTW the names of the classes and entities are *highly* suspicious. Contexts are supposed to handle *entities*. They aren't connections and yet `PostgreSqlDatabaseContext` suggests they are used like connections. You are trying to execute a raw query against the *Browser* entity set (singular?) but search for some other table by ID. If you wanted to load a browser from the *Browsers* set you could write `context.Browsers.FindAsync(someID)` for the primary key or `context.Browsers.SingleOrDefaultAsync(b=>b.MyID==id)`. – Panagiotis Kanavos Mar 21 '18 at 08:47
  • *And* you use string interpolation to construct dynamic queries. What would happen if someone typed `'; Drop table Users;--` as an ID value? – Panagiotis Kanavos Mar 21 '18 at 08:48
  • @PanagiotisKanavos - in response to "returned to the pool when you dispose the context", the context is disposed upon closing the using statement. What potential parts of this example do you see holding connections open? And, yes - contexts are supposed to handle `entities` as we were trying to do initially. This example is the result of frustration with the error. Naturally we have tried using EF as it would be used with MSSQL, but it just doesn´t work.. What kind of "smaller" example can I possibly provide? :/ – Marcus Mar 21 '18 at 08:56
  • One without any of *your* custom code. Even with SQL Server that static factory method would be *weird*. Why not specify the connection in the *context's configuration*? A single connection wrapped in `using` that runs a single command would show pool exhaustion if it exists. If you repeat it inside a loop 100 times and don't get pool exhaustion, you'll know there's no exhaustion bug – Panagiotis Kanavos Mar 21 '18 at 08:59
  • How about providing a simple, complete and minimal code sample that reproduces the issue? This way we can understand exactly what you're doing. – Shay Rojansky Mar 21 '18 at 09:19
  • I would say that the connections may not be returned to the pool because they are busy fetching data. You could enable the logs to know more. –  Mar 27 '18 at 14:36
  • 1
    @Marcus I get the same thing _ i believe USING does not close connections for Npgsql correctly. There's a look of chatter around this on github. But no solution – ManInMoon Dec 11 '18 at 15:19

0 Answers0