使用node-postgres进行LISTEN查询超时？

I have an "article" table on a Postgresql 9.1 database and a trigger that notifies a channel on each insert.

我在Postgresql 9.1数据库上有一个“文章”表和一个通知每个插入的通道的触发器。

I'd like to create a node.js script that catches those inserts and pushes notifications to connected clients using Socket.io. So far I'm using the node-postgres module to LISTEN to the channel but it seems the LISTEN query times out after about 10-15 seconds and stops catching the inserts. I could query a new listen when the timeout happens, but I'm not sure how to properly implement the continuation.

我想创建一个node.js脚本来捕获这些插入并使用Socket.io将通知推送到连接的客户端。到目前为止,我使用node-postgres模块LISTEN到通道,但似乎LISTEN查询在大约10-15秒后超时并停止捕获插入。我可以在超时发生时查询新的监听,但我不确定如何正确实现延续。

Here's my postgresql notification procedure:

这是我的postgresql通知程序:

CREATE FUNCTION article_insert_notify() RETURNS trigger AS $$
BEGIN
  NOTIFY "article_watcher";
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The trigger:

CREATE TRIGGER article_insert_trigger
AFTER INSERT ON article
FOR EACH ROW EXECUTE PROCEDURE article_insert_notify();

And the node.js code:

而node.js代码:

var pg = require ('pg'),
    pgConnection = "postgres://user:pass@localhost/db"

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

How can I ensure a fulltime LISTEN or how could I catch those timeouts to reissue a listen query ? Or maybe a module other than node-postgres offers more appropriate tools to do so ?

如何确保全职LISTEN或如何捕获这些超时以重新发出侦听查询?或者,node-postgres以外的模块可能提供更合适的工具吗?

2 个解决方案

#1

I got answer to my issue on the node-postgres repo. To quote Brianc:

我在node-postgres repo上得到了我的问题的答案。引用Brianc:

pg.connect is use to create pooled connections. Using a connection pool connection for listen events really isn't supported or a good idea though. [...] To 'listen' a connection by definition must stay open permanently. For a connection to stay open permanently it can never be returned to the connection pool.

pg.connect用于创建池连接。实际上,不支持使用连接池连接进行侦听事件,也不是一个好主意。 [...]按照定义“监听”连接必须永久保持打开状态。对于永久保持打开的连接,它永远不会返回到连接池。

The correct way to listen in this case is to use a standalone client:

在这种情况下,正确的侦听方式是使用独立客户端:

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

#2

LISTEN is supposed to last for the session lifetime or until you do UNLISTEN. So, as long as your code is running, notifications should be delivered. Note, that IIRC, postgresql gives no promises to deliver one notification per NOTIFY — if you have many inserts it may choose to deliver a single NOTIFY. Not sure about 9.1, they introduced LISTEN payload, so it may make a bit less sense.

LISTEN应该持续会话生命周期或直到你做UNLISTEN。因此,只要您的代码正在运行,就应该发送通知。注意,IIRC,postgresql没有承诺每个NOTIFY发送一个通知 - 如果你有很多插入,它可能会选择提供一个NOTIFY。不确定9.1,他们引入了LISTEN有效载荷,所以它可能会有点不太有意义。

#1