CommandTimeout for DataReader

[vko]

Hi All,

The problem is:
The command's timeout is not working for DataReader.
You can reproduce it when you try to SELECT data from a Large table.
Or, the problem appears when other operation blocks a rows in the table.

Code: Select all

	using (PgSqlConnection connection = new PgSqlConnection("string"))
			{
				PgSqlCommand command = connection.CreateCommand();
				command.CommandTimeout = 1;
				command.CommandText = "SELECT * FROM public.LargeTable";

				connection.Open();

				using (PgSqlDataReader reader = command.ExecuteReader()) // this line will block current thread forever
				{
					while (reader.Read()) ;
				}

				connection.Close();
			}

P.S. You will get a TimeoutException when you will execute commands such as pg_sleep().

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

This is the designed behaviour. CommandTimeout If server doesn't respond within the specified CommandTimeout period, an exception is thrown. When you execute the pg_sleep(), the server sleeps, and if it doesn't respond within the specified CommandTimeout period, the "Server did not respond within the specified timeout interval." exception occurs.

When you execute command.ExecuteReader(), you don't get this exception because the server responds on time. The application doesn't respond because it reads data to the memory, and the ExecuteReader() method doesn't return control until all the data is read.

If you want PgSqlDataReader to finish reading after some period of time, you may use the following workaround: execute the ExecuteReader() method with the CommandBehavior.SequentialAccess parameter. In this case, the data are not read to the memory buffer; instead a single row is read each time you call the Read method.

Code: Select all

static void Main(string[] args)
{
    using (PgSqlConnection connection = new PgSqlConnection("your connection string"))
    {
        connection.Open();
        PgSqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM public.LargeTable";

        connection.Open();
        Stopwatch watch = new Stopwatch();
        int count = 0;
        TimeSpan timeout = new TimeSpan(0, 0, 10);
        using (PgSqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            bool work = true;
            while (work)
            {
                watch.Start();
                work = reader.Read();
                watch.Stop();
                count++;
                if (watch.Elapsed >= timeout)
                {
                    Console.WriteLine("Timeout is reached. " +count+" rows are read");
                    break;
                }
            }
        }
        connection.Close();
    }
}