CommandTimeout for DataReader

CommandTimeout for DataReader

Postby 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().
vko
 
Posts: 1
Joined: Mon 04 Nov 2013 14:41

Re: CommandTimeout for DataReader

Postby 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: 1982
Joined: Wed 02 Nov 2011 09:44

Re: CommandTimeout for DataReader

Postby 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();
    }
}
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL