Timeout issue with PostgreSQL and Entity Framework

Question

When I try to query a PostgreSQL database through EntityFramework6.Npgsql with the following code:

using (MyDbContext context = new MyDbContext())
{
   var res = (from b in context.mytable select new { b.Name, b.Age }); 
   foreach (var row in res)
   {
      Console.WriteLine(row.Name + " - " + row.Age);
   }
}

I get a timeout exception after fetching few lines with the following error:

[Npgsql.NpgsqlException] : {"57014: canceling statement due to statement timeout"}

Message: 57014: canceling statement due to statement timeout

When I execute the same operation while fetching all the data to a List, the code works fine:

using (MyDbContext context = new MyDbContext())
{
   var res = (from b in context.mytable select new { b.Name, b.Age }).ToList(); 
   foreach (var row in res)
   {
      Console.WriteLine(row.Name + " - " + row.Age);
   }
}

I suspect that it is related to the way PostgreSQL manages its connection pool but I don't know how I could handle it correctly through Entity Framework.

Answer 1

This is probably related to the way Npgsql manages timeouts. In current versions, Npgsql sets the PostgreSQL statement_timeout variable which causes PostgreSQL to generate a timeout error after some time. The problem with this method is that statement_timeout is unreliable for this: it includes network time, client processing time, etc. so too much time spent on the client could make the server generate the error.

In your example, calling ToList() means that you immediately download all results, rather than iterate over them little by little. I do admit it's strange that such short client processing (i.e. Console.WriteLine) could introduce a delay sufficient to trigger a backend timeout (what is the command timeout set to?).

Note that the next major version of Npgsql will remove backend timeouts entirely because of the unreliable nature of statement_timeout - see https://github.com/npgsql/npgsql/issues/689. For now you can manually disable backend timeouts by setting the Backend Timeouts connection string parameter to false (see http://www.npgsql.org/doc/3.0/connection-string-parameters.html).