LISTEN request timeout with node-postgres?

Question

I have a “articles” table in a Postgresql 9.1 database and a trigger that notifies the channel of each insert.

I would like to create a node.js script that catches these inserts and issues notifications to connected clients using Socket.io. So far I am using the node-postgres module for LISTEN for the channel, but it seems that the LISTEN request expires in 10-15 seconds and stops capturing inserts. I can request a new listen when the timeout happens, but I'm not sure how to implement the continuation correctly.

Here is my postgresql notification procedure:

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

Trigger:

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

And 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 full time LISTEN or how can I catch these timeouts to listen to the listen request again? Or maybe a module other than node-postgres offers more suitable tools for this?

Answer 1

I got the answer to my question in the node-postgres repository. To quote Brianc:

pg.connect is used to create merged connections. Using connection pool connection to listen is really not supported or a good idea. [...] To “listen,” a connection, by definition, must remain open forever. For a connection to remain 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); }); 

Answer 2

LISTEN should last until the end of the session or until you do UNLISTEN . So, while your code is running, notifications should be delivered. Please note that IIRC, postgresql does not give promises for delivering one notification for NOTIFY - if you have many inserts, it can choose one NOTIFY . Not sure about 9.1, they introduced the LISTEN payload, so it might make sense a bit worse.