Clash Royale CLAN TAG#URR8PPP

Node PG driver stops listening to database events

I have a NodeJS service that uses PostGreSQL's event notification functionality to listen to the database for certain actions, and then execute Node functions as a response. How to set this up is documented in the node-postgres package's documentation, and at first glance appeared to function quite well.

The problem is that after running the Node service (that is listening to my database) for a "while" it stops listening to the database. By "a while" I mean that it is long enough that every time it happens I haven't noticed exactly when I last hit the service, but it was at least an hour or two. It might be longer ... in the realm of 2-4 hours?

I can confirm that as far as Postgres is concerned, after a while, the Node service is no longer connected to the database. But the Node service never seems to notice that it is not connected... it shows no errors, nor triggers the event that would normally be triggered by the connection being dropped (client.on('end',function()...)). I can't tell if this is a timeout caused by my Pool configuration in Node, or if the database is doing the disconnect. However, I assume that because there is more complexity in the setup of Node code to get this going it is more likely on the Node side. The PostGres side is really a few trigger functions that are basically the same thing as in the node-postgres documentation.

client.on('end',function()...)

When the Node service exhibits this "i'm not listening" behavior, it shows no errors or even messages... and debugging the Node code shows that it doesn't event jump into the handlers for, for example, the notificationProcessor() (shown below). So I'm a little at a loss for where to investigate. One challenge in investigating is that to replicate the behavior I have to set it running, and then leave it alone for "a while."

notificationProcessor()

So here is the relevant Node code. I am seeing the issue in NodeJS v8.7.0 and v9.0.0. I am using node-postgres v7.4.3. I am seeing the issue running Node on Mac and Linux. PostGres is 9.4.15 running on a separate Linux server from where Node runs.

// The PostGres driver for Node needs a setting for the Pool ... not sure why.
pg.defaults.poolSize = 1;
// Load the connection details (user, password, etc.)
var pg_connect = "postgres://" + config.pg_user + ":" + config.pg_pass + "@" + config.pg_server + "/" + config.pg_db;

// Setup the connection pool and ... connect to the database.
var pool = new pg.Pool(

connectionString: pg_connect,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000

);
// Critically, pass callback to the connection ... so start
// running the "executionListener()" when it connects.
pool.connect(executionListener);

pool.on(
'error',
function(err,client)
console.log("n-Error!- " + err.message);
console.log(" \----> Attempting to re-start Listening!n");
pool.connect(executionListener);

);

// What "channels" to monitor in the database for change events,
// and then what function to call when the event is found.
var monitoredChannels =
'executions':
cname: 'executions',
chandler: function(msg)
console.log("t[notification on executions] " + JSON.stringify(msg));
newExecution(msg);
,
cquery:
,
'updates':
cname: 'updates',
chandler: function(msg)
console.log("t[notification on updates] " + JSON.stringify(msg));
executionUpdate(msg);
,
cquery:

;

function notificationProcessor(notif)
var tpayload = notif.payload.split(',');
var resultLength = tpayload.length;
var resultAttributeCount = (resultLength - 1) / 2;
var payloadPointer = - 1;
// What DB table is the notification referencing?
var resultOut =
'table': tpayload[0]
;
for (var i = 0; i < resultAttributeCount; i++)
payloadPointer = payloadPointer + 2;
resultOut[tpayload[payloadPointer]] = tpayload[payloadPointer + 1];

// Once the data package from the notification is parsed into
// a useful form, we look at the array of channels we're watching,
// and pass the data to the handler for the appropriate channel.
return (monitoredChannels[notif.channel].chandler(resultOut))


// This is the main function used to monitor the database for events and
// react to them.
function executionListener(err, client, done)
// Handle errors in connection... if the connection
// fails, try to reconnect.
if(err)
console.log(err);
if (done != null) done();
pool.connect(executionListener);

console.log("[mPipeRunner] Listening to <" + config.pg_server + "/" + config.pg_db + "/" + config.schema + "> for executions");
// What to do when a notice (error) is sent by database.
client.on('notice', (msg) => console.warn('PGSQL Notice:', msg));
// When PG disconnects for some reason, what to do?
client.on('end',function()console.log('client disconnected'));
// When PG spits out a notification FROM A CHANNEL WE ARE LISTENING TO...
client.on('notification', notificationProcessor);

// Loop over the channels and initiate LISTENing to each one.
for (var chnl in monitoredChannels)
var channel = monitoredChannels[chnl];
channel.query = client.query("LISTEN " + channel.cname);

;


Thanks in advance for any insight!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.