Handle ConnectionLost in PgSqlDependency

Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Thu 29 Dec 2016 12:03

Hi guys

The NOTIFY (when a table is changed) works great :D

See example https://www.devart.com/dotconnect/postgresql/docs/Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlDependency.html#ExampleBookmark

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
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Fri 30 Dec 2016 11:26

The ConnectionLost event works only when LocalFailover is enabled. For more information, please refer to
https://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlConnection~ConnectionLost_EV.html
https://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlConnection~LocalFailover.html

To make the ConnectionLost event work, you need to add the following record to your code before opening connection:
Code: Select all
connection.LocalFailover = true;
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Fri 30 Dec 2016 12:58

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
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Fri 30 Dec 2016 13:33

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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Fri 30 Dec 2016 13:39

OK, so how do we detect when a connection is lost (so we can begin a reconnect procedure)???
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Fri 30 Dec 2016 15:57

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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Fri 30 Dec 2016 16:21

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 :) )?
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Tue 03 Jan 2017 13:24

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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Wed 04 Jan 2017 08:53

Thanks, that sound like a valid solution :) will you give us an update when the update is ready?
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Fri 06 Jan 2017 16:46

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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Postby tetzschner » Mon 09 Jan 2017 10:51

You guys rock :D 8) :)
tetzschner
 
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Postby Pinturiccio » Mon 16 Jan 2017 16:00

New build of dotConnect for PostgreSQL 7.7.819 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=34832
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL