SocketException opening a data reader

[taburaya]

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.

[Alexey]

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.

[taburaya]

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  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.

[Alexey]

You should increase cmd.CommandTimeout property.

[taburaya]

I tried to increase the CommandTimeout to 60, 120 and 150 seconds and it still doesn't work.
Have you reproduced the error ?

[Alexey]

Set CommandTimeout=600.

[taburaya]

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

[Alexey]

We will investigate this behaviour.
You will be notified on results as soon as possible.

[taburaya]

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.

[Alexey]

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".