LISTEN query timeout with node-postgres?

Question

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

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.

Here's my postgresql notification procedure:

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:

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 ?

Using pg-promise, complete examples with explanations: LISTEN / NOTIFY — vitaly-t
– vitaly-t, Commented Jun 4, 2016 at 15:49

lheurt · Accepted Answer · 2011-11-26 17:35:39Z

11

I got answer to my issue on the node-postgres repo. To quote 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.

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

answered Nov 26, 2011 at 17:35

lheurt

4093 silver badges9 bronze badges

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

1 Comment

vitaly-t Sep 17 at 19:48

This does not answer the original question of how to ensure a continued operation of LISTEN. The code shown here will fail on any interruption in communication, which happens a lot when running the app continuously.

Michael Krelin - hacker · Accepted Answer · 2011-11-24 20:59:31Z

0

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.

answered Nov 24, 2011 at 20:59

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

2 Comments

lheurt Over a year ago

Then the 15 seconds timeout is a client problem ? Can I solve that with node-postgres or am I just not having the good approach for what I want to do ?

Michael Krelin - hacker Over a year ago

I'm not really acquainted with node part and I'm not sure I fully understand what happens with pg and this whole environment after the code posted...

Jeb50 · Accepted Answer · 2021-08-20 21:31:34Z

0

A permanent connection in pg-promise is

db.connect({direct: true})
  .then(...)
  .catch(...);

answered Aug 20, 2021 at 21:31

Jeb50

7,13711 gold badges70 silver badges118 bronze badges

Collectives™ on Stack Overflow

LISTEN query timeout with node-postgres?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related