SocketException opening a data reader

SocketException opening a data reader

Postby taburaya » Tue 26 Sep 2006 01:41

Hi,

I'm having trouble executing the following code :
Code: Select all
    private void testFunc()
    {
      string sql = @"SELECT * FROM 
                empresa c LEFT OUTER JOIN
                             telefone_flat tf ON c.emp_id  = tf.emp_id 
                           WHERE
                             emp_excluida  = 0 AND   
                            (emp_razao_social  like :ptest OR   
                             emp_fantasia like :ptest )  AND
                            (emp_tipo & 1) = 1
                          ORDER BY
                            emp_razao_social";

      // connect to postgresql
      using (IDbConnection conn = Connect())
      {
        // create command object
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;

        // create parameter object
        PgSqlParameter psearch = new PgSqlParameter();
        psearch.ParameterName = "ptest";
        psearch.Value = "%A%";
        psearch.DbType = DbType.String;

        // add parameter to command
        cmd.Parameters.Add(psearch);

        // open reader
        using (IDataReader r = cmd.ExecuteReader())
        {
          // some processing code here...
        }
      }
    }



When I execute the code I get the error below :




A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SocketException (0x274c): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond]
System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) +1018043
System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) +119

[PgSqlException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond]
CoreLab.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) +387
CoreLab.Common.DbCommandBase.c(CommandBehavior A_0) +129
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)



After investigating the possible causes of the error, I found out that if I do one of the things listed below, the query executes OK :

1 - Remove the bitwise and operator in "(emp_tipo & 1) = 1" and make the clause "(emp_tipo = 1)".

2 - Use a more restrictive value for the "ptest" parameter, say "%TEST%", which reduces the returned rows from about 6000 to less than 200.

3 - Remove the join for the "telefone_flat" view, which is simply a view that selects data from a single table using the "DISTINCT ON" feature of PostgreSQL.

4 - Change the code to avoid using the parameter object and replace the parameter value directly in the sql string.

I'm using the latest version of PostgreSQLDirect (2.5.21).

Thanks in advance.
taburaya
 
Posts: 9
Joined: Mon 05 Jun 2006 12:20

Postby Alexey » Tue 26 Sep 2006 06:15

Send us small test project if possible to reproduce the problem; it is
desirable to use 'test' schema objects, otherwise include definition of
your own database objects. Do not use third party components.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby taburaya » Tue 26 Sep 2006 20:12

To reproduce the problem, do the following :

1 - Create the database objects using the script below.

Code: Select all

-- create tables
create table customer
(
   cust_id int,
   cust_name text,
   cust_address text
);

create table customer_telephone
(
   tel_id int,
   cust_id int,
   tel_areacode int,
   tel_number int
);

-- function to populate tables
CREATE OR REPLACE FUNCTION populateTestTables()
  RETURNS void AS
$BODY$
declare
   i int = 1;
   j int;
begin

   while (i <= 20000) loop

      insert into customer(cust_id, cust_name, cust_address)
      values (i, 'customer ' || i::text, 'address ' || i::text);

      j = 1;
      while (j <= 3) loop

         insert into customer_telephone(tel_id, cust_id, tel_areacode, tel_number)
         values((100000*i) + j, i, 11, (100000*i) + j);

         j = j + 1;

      end loop;

      i = i + 1;
   
   end loop;

end;
$BODY$
  language 'plpgsql' volatile;

-- create view
CREATE OR REPLACE VIEW v_customer_telephone as
SELECT DISTINCT ON (cust_id) cust_id, tel_areacode , tel_number
   FROM customer_telephone
  WHERE tel_id > 1000
  ORDER BY cust_id;



2 - Execute the populateTestTables() function once

3 - Create a C# project and add a program.cs file containing the code below.

Code: Select all

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using CoreLab.PostgreSql;

namespace testPostgreSQL
{
  class Program
  {
    private static PgSqlConnection Connect()
    {
      PgSqlConnection ret = new PgSqlConnection("Password=;User Id=;Host=;Database=;Connection Timeout=10;Max Pool Size=200;Schema=public");
      ret.Open();
      return ret;
    }

    static void Main(string[] args)
    {
      string sql = @"SELECT
                         *
                  FROM 
                     customer c LEFT OUTER JOIN
                    v_customer_telephone t ON c.cust_id  = t.cust_id 
                  WHERE
                    (c.cust_name like :ptest
                     OR   c.cust_address like :ptest )
                    and (c.cust_id & 1) = 1
                  ORDER BY
                    c.cust_name";

      using (PgSqlConnection conn = Connect())
      {
        PgSqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;

        PgSqlParameter psearch = new PgSqlParameter();
        psearch.ParameterName = "ptest";
        psearch.Value = "%s%";
        psearch.DbType = DbType.String;

        cmd.Parameters.Add(psearch);

        using (IDataReader r = cmd.ExecuteReader())
        {
          int i = 0;
          while (r.Read())
          {
            i++;
          }

          Console.WriteLine(String.Format("{0} records processed.", i));
        }
      }

    }
  }
}



4 - Compile your program.cs file and try to run it.
taburaya
 
Posts: 9
Joined: Mon 05 Jun 2006 12:20

Postby Alexey » Wed 27 Sep 2006 09:02

You should increase cmd.CommandTimeout property.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby taburaya » Wed 27 Sep 2006 19:50

I tried to increase the CommandTimeout to 60, 120 and 150 seconds and it still doesn't work.
Have you reproduced the error ?
taburaya
 
Posts: 9
Joined: Mon 05 Jun 2006 12:20

Postby Alexey » Thu 28 Sep 2006 05:56

Set CommandTimeout=600.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby taburaya » Mon 02 Oct 2006 16:35

Setting CommandTimeout = 600 returns the result after about 4 minutes. This is not acceptable.
I tried the same test using Npgsql and it returned in less than 1 second so this is clearly a bug in your provider
taburaya
 
Posts: 9
Joined: Mon 05 Jun 2006 12:20

Postby Alexey » Tue 03 Oct 2006 07:56

We will investigate this behaviour.
You will be notified on results as soon as possible.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby taburaya » Mon 16 Oct 2006 12:43

Any idea when will this be fixed ?
Users are complaining a lot because some parts of the system are not working due to this problem.
taburaya
 
Posts: 9
Joined: Mon 05 Jun 2006 12:20

Postby Alexey » Tue 17 Oct 2006 12:44

There is a signifficant difference between your query's plan when using parameter and without one. That's why your query is executed on a server differently. It seems to be PostgreSQL bug. We suggest you to apply to them.
NpgSql works quickly because it uses protocol of version 2.0, in which parameters binding doesn't take place. To have the same speed in PostgreSQLDirect .NET you should set Protocol property in the connection string to "Ver20".
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL