LISTEN query timeout with node-postgres?

Question

I have an "article" table on a Postgresql 9.1 database and a trigger that notifies a channel on each insert.

I'd like to create a node.js script that catches those inserts and pushes notifications to connected clients using Socket.io. So far I'm using the node-postgres module to LISTEN to the channel but it seems the LISTEN query times out after about 10-15 seconds and stops catching the inserts. I could query a new listen when the timeout happens, but I'm not sure how to properly implement the continuation.

Here's my postgresql notification procedure:

CREATE FUNCTION article_insert_notify() RETURNS trigger AS $$
BEGIN
  NOTIFY "article_watcher";
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The trigger:

CREATE TRIGGER article_insert_trigger
AFTER INSERT ON article
FOR EACH ROW EXECUTE PROCEDURE article_insert_notify();

And the node.js code:

var pg = require ('pg'),
    pgConnection = "postgres://user:pass@localhost/db"

pg.connect(pgConnection, function(err, client) {
    client.query('LISTEN "article_watcher"');
    client.on('notification', function(data) {
        console.log(data.payload);
    });
});

How can I ensure a fulltime LISTEN or how could I catch those timeouts to reissue a listen query ? Or maybe a module other than node-postgres offers more appropriate tools to do so ?

Answer 1

LISTEN is supposed to last for the session lifetime or until you do UNLISTEN. So, as long as your code is running, notifications should be delivered. Note, that IIRC, postgresql gives no promises to deliver one notification per NOTIFY — if you have many inserts it may choose to deliver a single NOTIFY. Not sure about 9.1, they introduced LISTEN payload, so it may make a bit less sense.

Answer 2

I got answer to my issue on the node-postgres repo. To quote Brianc:

pg.connect is use to create pooled connections. Using a connection pool connection for listen events really isn't supported or a good idea though. [...] To 'listen' a connection by definition must stay open permanently. For a connection to stay open permanently it can never be returned to the connection pool.

The correct way to listen in this case is to use a standalone client:

var pg = require ('pg'),
    pgConnectionString = "postgres://user:pass@localhost/db";

var client = new pg.Client(pgConnectionString);
client.connect();
client.query('LISTEN "article_watcher"');
client.on('notification', function(data) {
    console.log(data.payload);
});