Handle ConnectionLost in PgSqlDependency

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Handle ConnectionLost in PgSqlDependency

Post by 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/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

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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/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:

Code: Select all

connection.LocalFailover = true;

tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Post by 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

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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.

tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Post by 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)???

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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.

tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Post by 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 :) )?

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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.

tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Post by 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?

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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.

tetzschner
Posts: 6
Joined: Tue 14 Jul 2009 12:51

Re: Handle ConnectionLost in PgSqlDependency

Post by tetzschner » Mon 09 Jan 2017 10:51

You guys rock :D 8) :)

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Handle ConnectionLost in PgSqlDependency

Post by 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/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

Post Reply