Tuesday, November 29, 2011

Listen to Postgresql inserts with node.js

I've been playing with node.js a lot these last few days, and one of the things I've been trying to accomplish is to push live notifications to clients when an insert is done in a Postgresql database.
To start things on a good foot, I found this nice article detailing exactly my use case. I started right away and created a similar trigger on inserts on my Article table, with a stored procedure looking like this:
CREATE FUNCTION notify_article_insert() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('article_watcher', NEW.id || '###' || NEW.name );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Then, with a simple install of node.js 0.6.3 equiped with the node-postgres module, I tried the following script:
var pg = require ('pg'),
    pgConnectionString = "postgres://user:pass@localhost/db";

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

It worked just as expected... but only for 15 seconds. The query then seemed to somehow timeout, and I couldn't get anymore notification. What's worse is I couldn't find a way to catch this timeout when it happened and couldn't reissue the LISTEN query.
I dug a little and couldn't find any information on that subject. I finally got my answer straight from Brianc on the node-postgres module repo: 'pg.connect' is meant to handle pooled connections to the database, not persistent ones. For a LISTEN query, I need a standalone client that will stay open at all times:
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);
});

Problem solved! I ran the script for 2 days straight and it caught every single insert.

One last detail I learned along the way: Postgresql NOTIFY queries won't catch every single insert made. In case of a transaction with multiple inserts, a NOTIFY would proc just once in my tests. You have to use "Pg_notify" available on Postgresql 9+ and specify a payload to guarantee the notifications.

More articles on node.js are coming soon about websockets and reverse proxies.