Node.js and postgres LISTEN

Question

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

it seems like postgres database is not running probably, but you can always dig inside code-base and explore things... — Ashish
– Ashish, Commented Dec 28, 2014 at 13:07

Celso Agra · Accepted Answer · 2016-10-29 02:51:31Z

1

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.

answered Oct 29, 2016 at 2:51

Celso Agra

1,4992 gold badges18 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Node.js and postgres LISTEN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related