PgSqlException: CONNECTION FAILURE

PgSqlException: CONNECTION FAILURE

Postby russellmd » Mon 05 Nov 2007 02:13

Hi:

What is the root cause of this error?

Exception in aiSettings.get_setting():
CoreLab.PostgreSql.PgSqlException: CONNECTION FAILURE
at CoreLab.PostgreSql.PgSqlConnection.b()
at CoreLab.PostgreSql.PgSqlConnection.Open()
at aiSettings.get_setting(String setting) in c:\Inetpub\wwwroot\MDRDEV-AIMS\aims_simcoe\App_Code\common\aiSettings.cs:line 54

Ignore the class name and all that, it's a very basic database call - this happens in several of my applications that I use the CoreLab driver (3.25) with at random times. If I refresh the page a few times eventually it comes back again.

Checking the server, Postgres is running fine and so is IIS - anyone else run into this?

Here's the way I'm calling to the database as a general rule, again it's not just this function, it's several, this is just an easy example.

Code: Select all
public static String get_setting(String setting) {
        try {
            using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["cs_aims"].ToString())) {
                String query = "SELECT value " +
                               "FROM ai_tbl_settings " +
                               "WHERE setting=?";

                PgSqlCommand cmd = new PgSqlCommand(query, conn);
                cmd.Parameters.Add("setting", setting);

                conn.Open();
                return cmd.ExecuteScalar().ToString();
            }
        } catch (Exception e) {
            aiLog.error("Exception in aiSettings.get_setting():" + System.Environment.NewLine + e.ToString());
            return "";
        }
    }
russellmd
 
Posts: 8
Joined: Mon 05 Nov 2007 02:09

Postby Alexey » Mon 05 Nov 2007 08:23

Are you able to connect to your PostgreSQL server using PostgreSQLDirect .NET at all? Please specify the connection string used.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby russellmd » Mon 05 Nov 2007 13:57

When this problem starts happening I can't connect at all, but it clears itself up randomly after a few minutes.

IE, the server will be running for 2 weeks no problem, then all the sudden I get the error for 10-15 minutes, then it starts working again.


Here's the connection string:



Could I be reaching max_connections on postgres? would that throw that exception? It's a very busy website and max_connections is only set at 100.

Does the core labs driver do connection pooling by default?
russellmd
 
Posts: 8
Joined: Mon 05 Nov 2007 02:09

Postby Alexey » Tue 06 Nov 2007 07:44

It seems like PostgreSQL server's problem. If you say that your website is very busy, then this is quite common situation when a server fails to answer.
Yes, connection pooling is set by default. Try to increase max_connections parameter.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby cgrimster » Wed 02 Jan 2008 18:21

I've been having the same issue. It's doesn't happen often, but it has happened repeatedly.

Load does not seem to be an issue for me. I had this error on a development system today that was under no load whatsoever. I walked away from it and when I came back an hour later it was dead.

Something that may help pinpoint the cause of this: the last two times this has happened, I have had the same System.ArgumentException right before the CoreLab.PostgreSql.PgSqlException: CONNECTION FAILURE exceptions start. Here is the excerpt from my applications error log:

Code: Select all
[1/2/2008 1:02:15 PM] ERROR: Error in SomeClass.someMethod():
System.ArgumentException: Item has already been added. Key in dictionary: '25'  Key being added: '25'
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at System.Collections.Hashtable.Add(Object key, Object value)
   at CoreLab.PostgreSql.h.a(PgSqlConnection A_0, PgSqlType A_1, Int32 A_2, Boolean A_3, Boolean A_4, Boolean A_5)
   at CoreLab.PostgreSql.PgSqlDataReader.a(p[] A_0)
   at CoreLab.PostgreSql.PgSqlDataReader.e(Int32 A_0)
   at CoreLab.PostgreSql.PgSqlDataReader.h()
   at CoreLab.PostgreSql.a.q()
   at CoreLab.PostgreSql.v.g()
   at CoreLab.PostgreSql.v.a()
   at CoreLab.Common.DbConnectionFactory.a(DbConnectionBase A_0)
   at CoreLab.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
   at CoreLab.PostgreSql.PgSqlConnection.Open()
   at SomeClass.SomeMethod(String a, Int32 b) in c:\Inetpub\wwwroot\foo\SomeClass.cs:line 2140
[1/2/2008 1:04:39 PM] ERROR: Error in SomeOtherClass.aMethod():
CoreLab.PostgreSql.PgSqlException: CONNECTION FAILURE
   at CoreLab.PostgreSql.PgSqlConnection.d()
   at CoreLab.PostgreSql.PgSqlConnection.Open()
   at suMinistry.load() in c:\Inetpub\wwwroot\foo\SomeOtherClass.cs:line 1337


I doubt that Postgres is the cause, as when this happens, restarting IIS will bring the application back, whereas restarting Postgres doesn't fix anything.
cgrimster
 
Posts: 4
Joined: Wed 02 Jan 2008 18:13

Postby russellmd » Wed 02 Jan 2008 19:34

Yes, I'd like to stick my vote back in this thread, I get the _EXACT_ same error right before CONNECTION FAILURE starts up in my log files, I looked back for the past 5 months and every single time my connection dies, that error happens right before.

and for me also, restarting postgres doesn't fix the problem, only restarting IIS does.
russellmd
 
Posts: 8
Joined: Mon 05 Nov 2007 02:09

Postby Alexey.mdr » Thu 03 Jan 2008 10:41

This problem is being investigated. We will let you know as soon as we find out the reason.
Meanwhile try to increase connection timeout value and command timeout value.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby cgrimster » Tue 22 Jan 2008 15:44

Just checking in to see if you guys have had any luck finding the root cause of this issue.
cgrimster
 
Posts: 4
Joined: Wed 02 Jan 2008 18:13

Postby Alexey.mdr » Tue 22 Jan 2008 15:56

We will fix the problem in the next build.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby russellmd » Tue 19 Feb 2008 08:11

Any word on the new build? Date estimate?
russellmd
 
Posts: 8
Joined: Mon 05 Nov 2007 02:09

Postby Alexey.mdr » Tue 19 Feb 2008 09:53

We are planning to issue a new build next week.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for PostgreSQL