Node.js和Postgres LISTEN

Question

我想使用Heroku、PostgreSQL和Node.js，并设置每当我在我的PostgreSQL数据库中添加记录时，Node.js会将该行的内容打印到控制台。

我正在尝试按照以下说明进行设置：
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

这是Node.js代码：

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);
    });
});

这是在PostgreSQL数据库上执行的函数

    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();";

在将应用程序上传到Heroku后，我收到了这个错误。我做错了什么？

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

- Jefferson Hudson

---

请分享你的代码。 - Traveling Tech Guy

它在本地主机上工作吗？事件中的第72行是什么？ - Dan Kohn

修好了吗？嗯...已经有一段时间了。 - Macario

似乎Postgres数据库没有正常运行，但您可以随时深入代码库并探索事物... - Ashish

Answer 1

try to rename the variable:

String function = "CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ ";

到

String variableFunction = "CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ ";

function是一个保留字。

如果错误仍然存在，请尝试在本地主机上模拟相同的环境。

- Celso Agra