CommandTimeout for DataReader

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
vko
Posts: 1
Joined: Mon 04 Nov 2013 14:41

CommandTimeout for DataReader

Post by vko » Mon 04 Nov 2013 14:56

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: CommandTimeout for DataReader

Post by Pinturiccio » Wed 06 Nov 2013 11:02

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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: CommandTimeout for DataReader

Post by Pinturiccio » Mon 11 Nov 2013 12:54

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();
    }
}

Post Reply