What can cause a PgSqlConnection to abort or not even open?

What can cause a PgSqlConnection to abort or not even open?

Postby branrigg » Fri 11 Sep 2015 00:10

Specifically, if we call Open() on a PgSqlConnection to a remote IP, why would we sometimes see "An established connection was aborted..." or "An existing connection was forcibly closed by the remote host..." messages? Do these two specific exception messages have different causes?

Thanks for your help!
branrigg
 
Posts: 6
Joined: Mon 31 Aug 2015 23:07

Re: What can cause a PgSqlConnection to abort or not even open?

Postby Pinturiccio » Fri 11 Sep 2015 11:36

Probably these exceptions occur because of unstable network connection. If your connections have connection pooling enabled (it is enabled by default), try using the "Validate Connection=true;" connection string parameter to validate connections that are being taken from the pool. For more information, please refer to https://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlConnectionStringBuilder~ValidateConnection.html
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: What can cause a PgSqlConnection to abort or not even open?

Postby branrigg » Mon 14 Sep 2015 21:29

Ok thanks.

If we set a connection's "Validate Connection=true", do we need to need to set it back to false (so the default) after the connection is opened? I'd guess so, if the connection ends up back in the pool.

Also, when ValidateConnection is true, "the connection pool manager background validation is disabled". Is the connection pool manager's background validation re-enabled when ValdiateConnection is set back to false?

Thanks!
branrigg
 
Posts: 6
Joined: Mon 31 Aug 2015 23:07

Re: What can cause a PgSqlConnection to abort or not even open?

Postby Pinturiccio » Tue 15 Sep 2015 15:54

You don't need to set it back to false. Besides, you cannot do it because PgSqlConnection does not have a property for setting 'Validate Connection' .
The only way to set its value is to set it in the connection string when initializing the connection. You cannot change it after opening the connection.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: What can cause a PgSqlConnection to abort or not even open?

Postby branrigg » Tue 15 Sep 2015 22:16

Ok thanks. It looks like you can change Validate Connection to true before an Open() call succeeds.

So let's say we want to retry opening a PgSqlConnection after an Open() call fails. We should be able to add "Validate Connection=true" to the connection string in the Error event handler, right?
Basically we don't always know what our remote connectivity will be like. We want to typically rely on the existing background connection validation from the pool, and only add validating a connection manually ourselves (adding "Validate Connection=true" to the connection string) when needed.

So if we do Validate Connection after a failure, "the connection pool manager background validation is disabled". Are we guaranteed that for subsequent connections (like if we were to call Open() a couple minutes after it had failed), the background connection validation will be re-enabled?

Thanks again!
branrigg
 
Posts: 6
Joined: Mon 31 Aug 2015 23:07

Re: What can cause a PgSqlConnection to abort or not even open?

Postby Pinturiccio » Fri 18 Sep 2015 15:19

branrigg wrote:Ok thanks. It looks like you can change Validate Connection to true before an Open() call succeeds.

We are glad to hear that the issue is solved.

Background validation is performed for each connection. If you have 3 connections, background validation is performed for each of them. Background validation is performed every 30 seconds for a connection. If connection is lost and is taken from the pool during these 30 seconds, an error occurs.

If you use "Validate Connection=true;", the background validation is disabled. The validation will be performed for a connection each time it is taken from the pool in this case. This guarantees that an invalid connection will be deleted and a new one is created. If you use "Validate Connection=true;" in the connection string, this is more reliable than using background validation.

When you specify "Validate Connection=true;" in the connection string, background validation is disabled only for this connection. Background validation is still performed for other connection, including those created later.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: What can cause a PgSqlConnection to abort or not even open?

Postby branrigg » Fri 18 Sep 2015 16:40

Ok thank you. Great explanation, that's what we were wondering.
Thanks for helping us understand this!
branrigg
 
Posts: 6
Joined: Mon 31 Aug 2015 23:07


Return to dotConnect for PostgreSQL