Node-postgres: 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 :wink:

[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.

[vitaly-t]

@super-cache-money that didn't work? Did you log a bug against that library?

I want to know what happened, because awhile ago I started on an alternative (pg-listener), but never finished because it looked like there was no real need.

[super-cache-money]

I didn't log anything....
I seem to remember that it had the same problem of seizing up. When I checked after a few days after running it, notifications had stopped arriving.

When I have time I'll see if I can do a more detailed post mortem.

[pankleks]

Thanks guys, but I still try to understand the issue.

How I can get info that connection has been dropped.
I subscribed on Client "error" and "end" but none of them were fired, and notification stopped working anyway.

[pankleks]

I found problem with my case - in fact stupid mistake.
Connection used for notification was taken from pool, when I created dedication connection with new Client(...) is looks good.

Here is piece of TS code that could be reused - hope it helps.
In case connection is lost, it will try to reconnect immediately, if this fails, it will try again after some time.

    listen(fn: (message: { channel: string, payload: string }, client: Pg.Client) => void, ...channels: string[]) {
        let
            client = new Pg.Client(this._config);

        client.connect((err) => {
            if (err) {
                console.error(`Can't connect listener.`);

                setTimeout(() => {
                    this.listen(fn, ...channels);
                }, 1000 * LISTEN_RECONNECT_S);

                return;
            }

            client.on("notification", (message) => {
                fn(message, client);
            });

            client.on("error", (err) => {
                console.error(`Lost connection for listener.`);
                this.listen(fn, ...channels);
            });

            for (let e of channels)
                client.query(`LISTEN ${e}`);
        });
    }

[doesdev]

Just wanted to chime in with a solution in module form. I've been working with a milder iteration of this in prod for some time, but decided it might make for a useful module. There's nothing much to it.

More or less it will ping a notify every X seconds and keeps track of the last successful message, not bubbling the test message to the client. If it fails it will reopen the listener with Postgres and keep chugging. In the interim period (between a disconnect and reconnect) messages will be lost, so it isn't guaranteed that all messages come through in the event of a failure, It's useful if you don't need guaranteed messages 100% of the time, otherwise other solutions should be used.

Anyway, hertis: pg-ears

[brianc]

nice @doesdev - I've got some stuff on the 7.0 milestone to handle network partitions better & do research into why sometimes connections drop but don't report. I have a hunch it has to do w/ not setting heartbeats on the tcp connection to the backend, but I haven't dug in yet. I guess the somehwat good news is it's on my radar. :smile: