Connection to dB is timing out

[DavidSSL]

Description

When using SQLProvider my dB connection is timing out despite setting the command time out sufficiently high.

Repro steps

From a table containing 55 million entries, I try to retrieve the first 1 million using the following (apologies about formatting ):

let ctx = SQL.GetDataContext(TimeSpan.FromMinutes(20.0).Minutes)

let results =
    async {
        let! (field1, field2) =
            query {
                    for item in items do
                    take 1_000_000
                    select (item.Field1, item.Field2)
            } |> Seq.headAsync
        return (field1, field2)
    } |> Async.StartAsTask
printfn "Started getting results"
let (field1, field2) = results.Result
printfn "Hello %A %A!" field1 (Option.defaultValue (DateTime.MinValue) field2)

There is nothing wrong with the query because it ran fine with take 1000

Expected behavior

I expect the query to run to the end without problems because from the query generated by

Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %O")

which I ran, it took less than 8 mins to complete and my timeout is set to 20 mins.

Actual behavior

Not that I timed it precisely but the connection timed out within 5 mins with

System.AggregateException: One or more errors occurred. (Exception while reading from stream) ---> Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.IO.IOException: Unable to read data from the transport connection: Connection timed out. ---> System.Net.Sockets.SocketException (110): Connection timed out at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at Npgsql.NpgsqlReadBuffer.<>cDisplayClass31_0.<gEnsureLong|0>d.MoveNext() at Npgsql.NpgsqlReadBuffer.<>cDisplayClass31_0.<gEnsureLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlConnector.<>cDisplayClass161_0.<gReadMessageLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlDataReader.<>cDisplayClass37_0.<gReadLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlDataReader.Read() at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.FSharp-Data-Sql-Common-ISqlDataContext-ReadEntities@210.GenerateNext(IEnumerable1& next) at Microsoft.FSharp.Core.CompilerServices.GeneratedSequenceBase1.MoveNextImpl() in F:\workspace.1_work\1\s\src\fsharp\FSharp.Core\seqcore.fs:line 386 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at Microsoft.FSharp.Collections.SeqModule.ToArray[T](IEnumerable1 source) in F:\workspace.1\_work\1\s\src\fsharp\FSharp.Core\seq.fs:line 823 at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List1 ti) at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable1.System-Collections-Generic-IEnumerable1-GetEnumerator() at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext() at Microsoft.FSharp.Collections.SeqModule.Head[T](IEnumerable1 source) in F:\workspace.1_work\1\s\src\fsharp\FSharp.Core\seq.fs:line 1362 at FSharp.Data.Sql.AsyncOperations.getHeadAsync@51-1.Invoke(IEnumerable _arg1) at Microsoft.FSharp.Control.AsyncPrimitives.CallThenInvokeNoHijackCheck[a,b](AsyncActivation1 ctxt, FSharpFunc2 userCode, b result1) in F:\workspace.1_work\1\s\src\fsharp\FSharp.Core\async.fs:line 417 at <StartupCode$FSharp-Core>.$Async.Return@1066.Invoke(AsyncActivation1 ctxt) in F:\workspace.1\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 1066 at Microsoft.FSharp.Control.Trampoline.Execute(FSharpFunc2 firstAction) in F:\workspace.1_work\1\s\src\fsharp\FSharp.Core\async.fs:line 109 --- End of inner exception stack trace --- at System.Threading.Tasks.Task1.GetResultCore(Boolean waitCompletionNotification) at System.Threading.Tasks.Task1.get_Result() at <StartupCode$FSI_0004>.$FSI_0004.main@() Stopped due to error

Known workarounds

Related information

• Postgresql 9.6
• OS: Manjaro 18.1.3 Juhraya
• Branch (Not sure what this refers to)
• .NET core 3.0.100
• Query ran under 8 mins using Datagrip.

[Thorium]

Exact 5 mins sounds like a configuration issue rather than technical issue.

NpgsqlException is telling that it comes from drivers rather than SQLProvider itself. I know people have ran longer queries using SqlProvider, but maybe other databases.

Btw, your source code doesn't make so much sense, it takes 1 000 000 and then only one, by Seq.head. But that's probably just to demonstrate the issue, I suppose.

[DavidSSL]

Hi Thorium,

You are absolutely right in saying that my example is non-sensical as I am experimenting with SQLProvider and I let that slip through.

However the fact is that from a high level point of view I have an API provided to me using which I pass a timeout value.

SQL.GetDataContext(TimeSpan.FromMinutes(20.0).Minutes)

I would not expect to have to know anything about what Npgsql is doing underneath but I would expect that the timeout value would just be patched through.

Am I wrong in this aspect?

[Thorium]

Yes you are correct, but to fix the issue we have to know what timeout is being hit, to modify the timeout-setting to configure it.

[DavidSSL]

@Thorium thank your answer. After further testing, I cannot reproduce the problem anymore. In fact, I can validate that things works as they should.

What I mean by this is that I ran the same operation with 5 minutes as the command timeout and it did in fact time out after 4 minutes.

Then I increased the command timeout to 15 minutes and this time it returned the result after 9 minutes.

Two differences this time though:

1. I was running the query using a different machine (a less powerful one in fact)
2. I was running the query from home fairly late in the evening when no one else was hammering the dB server presumably.