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.
Very useful, thank you!
ReplyDeletethaaanks so much,
ReplyDeleteIs there any way to do it throw "ODBC" ?
Very much useful article
ReplyDeleteInternship providing companies in chennai | Where to do internship | internship opportunities in Chennai | internship offer letter | What internship should i do | How internship works | how many internships should i do ? | internship and inplant training difference | internship guidelines for students | why internship is necessary
Very interesting post and thanks for your knowledgeable sharing with us. Keep doing well!
ReplyDeleteVirginia Online Divorce
Divorce Lawyer Consultation Cost
Divorce in Virginia with Child
Great post and I know more details from this article. Thank you!
ReplyDeletepvc foam board manufacturers kerala