Periodically we get Failed to establish a connection error

[Itay Herskovits]

Hi All,

We have started to used npgsql in more robust way and since then we get Npgsql.NpgsqlException: Failed to establish a connection to '*.rds.amazonaws.com'.

The code we use in .NET is:

private DataTable GetTable(string sql, string connectionString)
{
    DataTable table = new DataTable();

 
    NpgsqlDataAdapter npgsqlDataAdapter = new NpgsqlDataAdapter(sql, new NpgsqlConnection(connectionString)); 

    npgsqlDataAdapter.Fill(table); 

    return table;
}

Any idea why we getting this error or do you have better approach in .NET code?

Thanks

[Shay Rojansky]

This would ordinarily be an issue in your environment somehow, and not necessarily with Npgsql. Can you provide some more details?

• Full stack trace
• Is the connection failure intermittent (i.e. occurs sporadically, inconsistently although most connection attempts succeed)? Can you reproduce it?
  
• Does the same code work without any exception with lower load?
• Can you reproduce the failure outside .NET with, say, psql or pgadmin? If so that proves the problem is external.

Shay

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/b6f2fc16-bc6b-4045-a1b1-b8d8507a2a09%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Emil Lenngren]

I think you are leaking resources. Be sure you close the connection when you have filled the data table. For example by wrapping everything in a "using" statement.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/CADT4RqAh53z0BMgUJ2fjTMO1M_GZf5yt-k_vgE_AnRBd3G2-Lw%40mail.gmail.com.

[Luiz Henrique]

"using" statements would be a good solution to make sure you aren't having leaking resources. 

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/CAO1O6sfvdxpUCFOnp-KJRhihfFxmarimU3fPYF9HdBs0%3Dw%2BjBg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Luiz Henrique

Twiter: @luizhds

Facebook: http://www.facebook.com/luizhds

Linked In: http://www.linkedin.com/in/luizhds

[rivlin...@gmail.com]

I replaced the original code with the following:

NpgsqlDataAdapter npgsqlDataAdapter = (NpgsqlDataAdapter)adapter;

using (IDbConnection connection = npgsqlDataAdapter.SelectCommand.Connection)
{
connection.Open();

using (IDataReader reader = npgsqlDataAdapter.SelectCommand.ExecuteReader())
{
table.Load(reader, LoadOption.OverwriteChanges);
reader.Close();
}

connection.Close();
}

and still got the same sporadic error with the following trace:

Npgsql.NpgsqlException: Failed to establish a connection to 'someserver.rds.amazonaws.com'. at Npgsql.NpgsqlClosedState.Open(NpgsqlConnector context, Int32 timeout) at Npgsql.NpgsqlConnector.Open() at Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection) at Npgsql.NpgsqlConnectorPool.RequestPooledConnectorInternal(NpgsqlConnection Connection) at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection) at Npgsql.NpgsqlConnection.Open() at Durados.DataAccess.PostgreAccess.Fill(IDataAdapter adapter, DataTable table) at Durados.DataAccess.SqlAccess.FillDataTable(View view, Int32 page, Int32 pageSize, Filter filter, String sortColumn, DataSet dataSet, SortDirection direction, Int32& totalRowCount, ParentField parentField, String join, String selectStatement, BeforeSelectEventHandler beforeSelectCallback, AfterSelectEventHandler afterSelectCallback) at Durados.DataAccess.SqlAccess.FillDataTable(View view, Int32 page, Int32 pageSize, Filter filter, String sortColumn, SortDirection direction, Int32& totalRowCount, ParentField parentField, String join, BeforeSelectEventHandler beforeSelectCallback, AfterSelectEventHandler afterSelectCallback) at Durados.DataAccess.SqlAccess.FillPage(View view, Int32 page, Int32 pageSize, Filter filter, Nullable`1 search, Nullable`1 useLike, Dictionary`2 sortColumns, Int32& rowCount, BeforeSelectEventHandler beforeSelectCallback, AfterSelectEventHandler afterSelectCallback) at Durados.DataAccess.SqlAccess.FillPage(View view, Int32 page, Int32 pageSize, Dictionary`2 values, Nullable`1 search, Nullable`1 useLike, Dictionary`2 sortColumns, Int32& rowCount, BeforeSelectEventHandler beforeSelectCallback, AfterSelectEventHandler afterSelectCallback) at Durados.Web.Mvc.UI.Helpers.RestHelper.Get(View view, Boolean withSelectOptions, Boolean withFilterOptions, Int32 page, Int32 pageSize, Dictionary`2[] filter, String search, Dictionary`2[] sort, Int32& rowCount, Boolean deep, BeforeSelectEventHandler beforeSelectCallback, AfterSelectEventHandler afterSelectCallback, Boolean returnDataView, Boolean descriptive) at BackAnd.Web.Api.Controllers.viewDataController.Get(String name, Nullable`1 withSelectOptions, Nullable`1 withFilterOptions, Nullable`1 pageNumber, Nullable`1 pageSize, String filter, String sort, String search, Nullable`1 deep, Boolean descriptive)

I could not reproduce outside .net
The code is running on a win2012 server

[Shay Rojansky]

The code sample shows some strange code practices... and specifically doesn't show the creation of the NpgsqlConnection object.

The more standard way to use data adaptors can be seen here: https://msdn.microsoft.com/en-us/library/system.data.idbdataadapter.selectcommand(v=vs.110).aspx

In a nutshell, when you want to open a connection to the database, you should instantiate a new NpgsqlConnection object with the proper connection string. You should then create an NpgsqlCommand for your select command, and assign its Connection property to point to your NpgsqlConnection. Then you create the NpgsqlDataAdapter with the command, and you should be good to go.

Also, please post your connection string (without the password), do you use connection pooling?

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/11f86bf2-0f26-4bb4-b11c-91b4117042f5%40googlegroups.com.