Page 1 of 1
Handle ConnectionLost in PgSqlDependency
Posted: Thu 29 Dec 2016 12:03
by tetzschner
Hi guys
The NOTIFY (when a table is changed) works great
See example
https://www.devart.com/dotconnect/postg ... leBookmark
BUT...
How do I handle the event when the connection is lost???
I did try (but ConnectionLost in PgSqlConnection is never fired):
Code: Select all
PgSqlConnection connection = new PgSqlConnection("User Id = xxx; Password = xxx; host = xxx; Port = 5432; database = xxx;");
depend = new PgSqlDependency();
depend.OnChange += new OnChangeEventHandler(dependency_OnChange);
PgSqlCommand selectCommand = new PgSqlCommand("SELECT * FROM public.xxx", connection);
depend.AddCommandDependency(selectCommand);
//depend.CheckTimeout = 500;
connection.ConnectionLost += new ConnectionLostEventHandler(connection_ConnectionLost);
connection.Open();
depend.Start(connection);
Kind regards, Ole
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 11:26
by Pinturiccio
The ConnectionLost event works only when LocalFailover is enabled. For more information, please refer to
https://www.devart.com/dotconnect/postg ... st_EV.html
https://www.devart.com/dotconnect/postg ... lover.html
To make the ConnectionLost event work, you need to add the following record to your code before opening connection:
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 12:58
by tetzschner
Thanks Pinturiccio, but that does not change anything. The ConnectionLost is never fired. I've also tried StateChange but that's never fired.
When cuting the network-connection, no events are fired
when I reconnect the network again, the output in VS2015:
Code: Select all
Exception thrown: 'System.IO.IOException' in System.dll
Exception thrown: 'System.IO.IOException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Data.PostgreSql.PgSqlException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Common.j' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Data.PostgreSql.PgSqlException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Common.j' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Data.PostgreSql.PgSqlException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Common.j' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Data.PostgreSql.PgSqlException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Common.j' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Data.PostgreSql.PgSqlException' in Devart.Data.PostgreSql.dll
Exception thrown: 'Devart.Common.j' in Devart.Data.PostgreSql.dll
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 13:33
by Pinturiccio
If you want the ConnectionLost event to be called during the usage of PgSqlDependency, there is no such possibility.
PgSqlDependency creates a separate connection within itself, in which it monitors notifications. The ConnectionLost event is not initialized in this connection. Besides, other more low-level commands that do not use LocalFailover are used for notification monitoring. That’s why the implementation of ConnectionLost for PgSqlDependency is impossible.
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 13:39
by tetzschner
OK, so how do we detect when a connection is lost (so we can begin a reconnect procedure)???
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 15:57
by Pinturiccio
If you want to trace connection loss, you can create a separate thread, create a connection within it and call the Ping method from connection periodically. If the false value is returned, you need to recreate PgSqlDependency object.
If connection loss takes place, you need to recreate the PgSqlDependency object, since during initialization, PgSqlDependency executes commands of the session level for monitoring notifications within this session. After connection loss, there is no session and you need to set a new session for initializing monitoring for it. This is one more reason why ConnectionLost cannot be implemented for PgSqlDependency object.
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 30 Dec 2016 16:21
by tetzschner
This is not a valid solution. If NOTIFY runs over connection A, it is not a solution to create a new connection (B) and watch that. In this situation, we will only be notified that connection B is down. What we need is to monitor connection A. Could you please make a fix for this (see it as Devarts New Year's resolution
)?
Re: Handle ConnectionLost in PgSqlDependency
Posted: Tue 03 Jan 2017 13:24
by Pinturiccio
A "connection A" is not related to the PgSqlDependency class. You can pass a connection string to the PgSqlDependency.Start method. An internal connection is created for constant monitoring of a database, and it is used in a background thread. The reason why Lost Connection is not applicable to PgSqlDependency is described above. Even if an internal connection of a PgSqlDependency object had the ConnectionLost event, simple connection reopening wouldn’t help in such a case.
We will investigate the possibility to add an Error event to PgSqlDependency class. With this event you will be able to catch an error when a connection is lost. After this you can recreate the PgSqlDependency object in this event in order to continue its work.
Re: Handle ConnectionLost in PgSqlDependency
Posted: Wed 04 Jan 2017 08:53
by tetzschner
Thanks, that sound like a valid solution
will you give us an update when the update is ready?
Re: Handle ConnectionLost in PgSqlDependency
Posted: Fri 06 Jan 2017 16:46
by Pinturiccio
We have added the Error event to the PgSqlDependency class. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
Re: Handle ConnectionLost in PgSqlDependency
Posted: Mon 09 Jan 2017 10:51
by tetzschner
Re: Handle ConnectionLost in PgSqlDependency
Posted: Mon 16 Jan 2017 16:00
by Pinturiccio
New build of dotConnect for PostgreSQL 7.7.819 is available for download now!
It can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to
viewtopic.php?t=34832
