LISTEN notifications stop after a while

[super-cache-money]

I'm using LISTEN to be notified of changes. It used to work perfectly when I was listening to a DB on localhost. However, now that I've moved the DB over to another VM, I'm encountering some problems where my client loses "sync" with the DB and stops receiving notifications after some time (in order of minutes/hours).

I'm not using the connection pool, here's how I connect and listen to updates:

var client = new pg.Client(pgConString);
client.connect(function(err, client) {
    if(err) {
      throw err;
    }
    client.on('notification', function(msg) {
        console.log(msg.payload);
    });
    client.on('error', function(error) {
      console.error('This never even runs:', error);
    });
    var query = client.query("LISTEN watchers");
});

Is it possible keep this listener working indefinitely? Am I doing something silly? What is supposed to happen after a momentary network failure? Do I have to resign to polling the DB and diffing changes?

[vitaly-t]

Did you check what other events, if any, does your client object receive when it loses the connection? Is there some sort of done or end event you can handle for the client object?

[super-cache-money]

I've just listened for all of end, done, and error. None are fired when I lose connection.

[vitaly-t]

One of the main reasons why this library moved mostly into the use of the connection pool is because it is very awkward dealing with dropped connections, while the pool can maintain connections automatically.

But with the event listening it is impossible to use the pool, because each connection in the pool has an expiration on it.

If you cannot detect a dropped connection through the client interface, you can try and detect it otherwise, and once detected, re-establish the watcher.

If you suspect a networking failure, you can listen for that, somehow.

Like I said, static connections are awkward. And listeners are used by very few people, so there is no standard solution to this, it seems. I would suggest to inquire about this on Stack Overflow.

[super-cache-money]

Thank you @vitaly-t. If fixing this falls outside of the scope of this module, I'm happy to close this issue.

[vitaly-t]

@super-cache-money I'm 50% certain that it does, so do as you please ;)

[super-cache-money]

FWIW, I am considering using this library to listen for events on the same server as the DB, so that the connection never fails. I'll then pass the notifications to another messaging client (maybe rabbitmq? ) which can broadcast robustly to the remote servers. Let me know if that sounds dumb.

[vitaly-t]

Sorry, I'm not an expert at either to pass a judgement. Try it, see if it works ;)

[ReinsBrain]

Still open - I'd very much like to use NOTIFY/LISTEN and hoping work on pg may have since resolved this issue since it was posted. Can anyone comment? If the problem persists, I was thinking that I could simply re-establish the connection ( and LISTEN query ) on a routine basis ( say every minute )... thoughts?

[vitaly-t]

I started pg-listener some time ago, but never finished it, hopefully later.

As of today I know of only one usable implementation: node-pg-pubsub.

[pankleks]

Hi - I'm facing same issue.
I understand that connection on which I'm issuing pg.on('notification') has been dropped, and due to connection pooling - it was invisible.
So it there a way to get notification on connection drop/error so I can issue new pg.on(...) on new connection?

[vitaly-t]

@pankleks as of today, node-pg-pubsub is your best shot 😉

[super-cache-money]

@vitaly-t @pankleks yeah, tried that with no success too :(

[super-cache-money]

ended up routing the notifications through rabbitmq. That was a hassle.