Environment: postgres 9.6.3, npgsql 3.2.5, .net 4.5.1
We execute an import/export transfer task (large amount of data to transfert row by row), that use a cursor during read and a separate connection to perform write.
What happens is that if the write operation takes a bit long (few seconds), we receive the following error in DataReader.Read();:
Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.IO.IOException:
Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host. --->
System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
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.ReadBuffer.<Ensure>d__27.MoveNext()
at Npgsql.ReadBuffer.<Ensure>d__27.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
at Npgsql.NpgsqlDataReader.<Read>d__28.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.Read()
According to the duration of the pause (3-4 seconds) we may fetch: 120, 240, ... records, and than the exception raise. If we do not pause the read, and we read all data in one loop, it works.
BTW we cannot read all data in memory for space allocation reason.
We see that we receive a: DataReader_ReaderClosed event during the failing call to DataReader.Read();
Here our PG function skeleton:
CREATE FUNCTION "public"."myPGFunction" (INOUT cv_1 refcursor DEFAULT NULL::refcursor) RETURNS refcursor
AS $$
begin
OPEN cv_1 FOR
SELECT ....
FROM .... a;
end;
$$ LANGUAGE plpgsql
And the code is something like:
_Connection = new NpgsqlConnection("");
_Connection.Open();
_Transaction = _Connection.BeginTransaction();
_SelectCommand = _Connection.CreateCommand();
_SelectCommand.CommandText = "myPGFunction";
_SelectCommand.Connection = _Connection;
_SelectCommand.CommandType = CommandType.StoredProcedure;
NpgsqlParameter parCur = new NpgsqlParameter("cv_1".ToLower(), NpgsqlTypes.NpgsqlDbType.Refcursor);
parCur.Direction = ParameterDirection.InputOutput;
parCur.Value = "refcur";
_SelectCommand.Parameters.Add(parCur);
_SelectCommand.Transaction = _Transaction;
_SelectCommand.ExecuteNonQuery();
_SelectCommand.CommandText = "FETCH ALL IN \"refcur\"";
_SelectCommand.CommandType = CommandType.Text;
_DataReader = _SelectCommand.ExecuteReader();
here the read loop :
while(_DataReader.Read())
{
// Read Data
// Transfer data taking up to 3-4 seconds
...
}
So is there any timeout to perform a _DataReader.Read() loop following an ExecuteReader() using CommandText = "FETCH ALL IN \"refcur\""; ?
BTW we tested several values of Command Timeout, or even the special "Internal Command Timeout" or TCP keep alive, etc... without any effect on our issue.