I want to use Heroku, PostgreSQL, and Node.js, and set it up so that anytime I add a record in my postgres database Node.js prints the contents of that row to the console.
I am trying to set it up as these instruct:
http://lheurt.blogspot.com/2011/11/listen-to-postgresql-inserts-with.html
http://bjorngylling.com/2011-04-13/postgres-listen-notify-with-node-js.html
here is the node.js code
var pg = require('pg');
conString = '/*my database connection*/';
var client = new pg.Client(conString);
client.connect(function(err) {
if(err) {
return console.error('could not connect to postgres', err);
}
client.connect();
client.query('LISTEN "loc_update"');
client.on('notification', function(data) {
console.log(data.payload);
});
});
Here is the function executed on the postgres database
String function = "CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ "
+ "DECLARE "
+ "BEGIN "
+ "PERFORM pg_notify('loc_update', TG_TABLE_NAME || ',longitude,' || NEW.longitude || ',latitude,' || NEW.latitude );"
+ "RETURN new;"
+ "END;"
+ "$$ LANGUAGE plpgsql;";
String trigger = "CREATE TRIGGER location_update AFTER INSERT ON device "
+ "FOR EACH ROW EXECUTE PROCEDURE notify_trigger();";
After uploading to Heroku I receive this error. What am I doing wrong?
2013-10-13T22:40:21.470310+00:00 heroku[web.1]: Starting process with command `node web.js`
2013-10-13T22:40:23.697134+00:00 app[web.1]:
2013-10-13T22:40:23.727555+00:00 app[web.1]: events.js:72
2013-10-13T22:40:23.727822+00:00 app[web.1]: throw er; // Unhandled 'error' event
2013-10-13T22:40:23.727822+00:00 app[web.1]: ^
2013-10-13T22:40:23.784576+00:00 app[web.1]: error: invalid frontend message type 0
2013-10-13T22:40:23.784576+00:00 app[web.1]: at p.parseE (/app/node_modules/pg/lib/connection.js:473:11)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at p.parseMessage (/app/node_modules/pg/lib/connection.js:348:17)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:84:22)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at Socket.EventEmitter.emit (events.js:117:20)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at Socket.<anonymous> (_stream_readable.js:746:14)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at Socket.EventEmitter.emit (events.js:92:17)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at emitReadable_ (_stream_readable.js:408:10)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at emitReadable (_stream_readable.js:404:5)
2013-10-13T22:40:23.784576+00:00 app[web.1]: at readableAddChunk (_stream_readable.js:165:9)
2013-10-13T22:40:23.784787+00:00 app[web.1]: at Socket.Readable.push (_stream_readable.js:127:10)
2013-10-13T22:40:25.128299+00:00 heroku[web.1]: Process exited with status 8
2013-10-13T22:40:25.133342+00:00 heroku[web.1]: State changed from starting to crashed
Use node-postgres Module The node-postgres module is an npm package that allows you to connect to and interact with a PostgreSQL database. There are two options you can use to connect Node with PostgreSQL using the node-postgres module: a single client or a connection pool.
PostgreSQL offers an asynchronous Publish-Subscribe functionality in the form of the LISTEN and NOTIFY commands. A client registers its interest in a particular channel (a.k.a. topic or event) with the LISTEN command (and can stop listening with the UNLISTEN command).
PostgreSQL client is defined as connect to the database server using command, utility or third party tool. Basically we are using psql utility to connect the database server from OS interface, also we are using pg_admin tool to interact with database server using client interface.
try to rename the variable:
String function = "CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ ";
to
String variableFunction = "CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ ";
function is a reserved word.
If the error persists, try to simulate the same environment on localhost.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With