CONNECTION FAILURE after Postgres restart

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
marek.kulvejt
Posts: 5
Joined: Mon 06 Jul 2009 08:50

CONNECTION FAILURE after Postgres restart

Post by marek.kulvejt » Mon 06 Jul 2009 09:00

Hello everybody,

we have a problem with reconnection after DB server restart. First we get Socket anf "FATAL" exception (which is correct - we are stopping the SQL server). But then, when the server runs again, we are not able to make a connection and we get "CONNECTION FAILURE" exception. Clearing connection pool does not have any effect. When we restart the .NET application. Everything works well again (-> no problem with the SQL server itself).

We get the following sequence of exceptions:

Devart.Data.PostgreSql.PgSqlException: An established connection was aborted by the software in your host machine ---> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at Devart.Data.PostgreSql.a.a(Exception A_0)
at Devart.Data.PostgreSql.a.a()
at Devart.Data.PostgreSql.a.f()
at Devart.Data.PostgreSql.t.a(Char A_0)
at Devart.Data.PostgreSql.t.a(Boolean A_0, Boolean A_1)
at Devart.Data.PostgreSql.t.af()
at Devart.Data.PostgreSql.t.b(String A_0)
at Devart.Data.PostgreSql.t.l()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
at Devart.Data.PostgreSql.n.a(IsolationLevel A_0)
at Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
at ESG.Db.Relational.Shared.RelationalDataAccess.ExecutePlan(DbConnection connection, DatabaseOperationPlan plan, EsgTraceSource serverTraceSource)
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: An established connection was aborted by the software in your host machine ---> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at ESG.Db.Relational.Shared.RelationalDataAccess.SynchronizeChanges(Credentials credentials, IEnumerable`1 records, ProcessQueriesResult& operationResult, DatabaseOperationPlan plan, Exception exc, Dictionary`2 usedServerVariables)
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: FATAL: the database system is shutting down

at Devart.Data.PostgreSql.n.x()
at Devart.Data.PostgreSql.n.a()
at Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: CONNECTION FAILURE
at Devart.Data.PostgreSql.PgSqlConnection.b()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: CONNECTION FAILURE
at Devart.Data.PostgreSql.PgSqlConnection.b()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)


Thanks in advance,
Marek

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Jul 2009 11:17

It is necessary to clear the pool after the server restarts. For this purpose, please call the Devart.Data.PostgreSql.PgSqlConnection.ClearPool method. The ClearAllPools method does not immediately clear the pools (operation is started in approximately 30 seconds).

As an alternative, you can set the Pooling parameter of your connection string to false. This will disable pooling.

marek.kulvejt
Posts: 5
Joined: Mon 06 Jul 2009 08:50

ClearPool vs. ClearAllPools

Post by marek.kulvejt » Wed 08 Jul 2009 17:48

Thank you for the answer. I have some additional questions:

1) What is the proper usage of ClearPool()?

- as I use 'using (PqSqlConnection....' - should I call it immediatelly when it happens?

2) What is the difference between ClearPool() and ClearAllPools()? Why one should and other should not work in my scenario? I mean: it does not start working even after 30 seconds. It stops working completely 'forever'.

3) What is the performance impact of not using the connection pool?

Thans for the answers,
Marek

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 09 Jul 2009 12:51

1) When the program leaves the using(PgSqlConnection...) block, connection returns to pool. So, you need to force explicit pools clearance.

2) Pools are automatically cleared if a connection is idle for some time or closed by the server. Call ClearPool/ClearAllPools to force explicit pools clearance. ClearPool clears the pool matching ConnectionString of provided PgSqlConnection object. ClearAllPools clears all pools associated with the connection.

I cannot reproduce the problem with the ClearAllPools method, it clears a pool within 30 seconds. Please try the latest version of dotConnect for PostgreSQL (5.20.33) and notify us about the result.

3) The performance impact depends on your application architecture. For example:
a) if you open one global connectin on your application startup and close it when application finishes its work, connection pooling is not needed at all;
b) if your application opens and closes connections with the same connection string frequently, it will increase performance because the same internal connection from the pool will be used every time. In this case we cannot tell you the exact performance impact, it depends on many parameters (e.g., local or remote server, time of response, etc).

marek.kulvejt
Posts: 5
Joined: Mon 06 Jul 2009 08:50

latest version download?

Post by marek.kulvejt » Thu 09 Jul 2009 14:42

Where can I find the 5.* version? Only 4.5 is present in the download section (...The latest version is 4.50.33 (21-May-2009)....).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 09 Jul 2009 15:11

Sorry, the 4.50.33 version is the latest one at the moment.

marek.kulvejt
Posts: 5
Joined: Mon 06 Jul 2009 08:50

Post by marek.kulvejt » Mon 13 Jul 2009 14:57

Hello,

allow me one more question please. I'll put it this way:

As we always use a new connection when we want to communicate with the database - something like this:

using (PgSqlConnection conn = new PgSqlConnection(connString))
{
conn.Open();

.. do some DB work
}

How can happen that after the failure I've already described and calling ClearAllPools/ClearPools(), the dotConnect is not able to connect to the database anymore? When the process is restarted then everything works well again. So there are no problems with the databe server itself, networking etc...

According to me, there must be some "static" state that somehow breaks the functionality of the dotConnect.

Could there be something like this? The problem is really weird and it cannot be reproduced in a predictable manner. It just happens sometimes.

Best regards,
Marek

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 14 Jul 2009 10:48

Probably, this is a connection pooling issue.

When connection is opened for the first time, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is an exact match of the existing pool, the connection from the existing pool is returned to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it.

If after server restart you obtain the error from your first post in this thread, this indicates that the connection pool is not cleaned yet even if you call the ClearAllPools/ClearPools method (operation is started in approximately 30 seconds).

Post Reply