Postgresql connection timed out in node.js and pg

Question

I am new to node, postgresql, and to the whole web development business. I am currently writing a simple app which connects to a postgres database and display the content of a table in a web view. The app will be hosted in OpenShift.

My main entry is in server.js:

var pg = require('pg');
pg.connect(connection_string, function(err, client) {
    // handle error
    // save client: app.client = client;
});

Now, to handle the GET / request:

function handle_request(req, res){
    app.client.query('...', function(err, result){
        if (err) throw err; // Will handle error later, crash for now
        res.render( ... );  // Render the web view with the result
    });
}

My app seems to work: the table is rendered in the web view correctly, and it works for multiple connections (different web clients from different devices). However, if there is no request for a couple of minutes, then subsequent request will crash the app with time out information. Here is the stack information:

/home/hai/myapp/server.js:98
            if (err) throw err;
                           ^
Error: This socket is closed.
    at Socket._write (net.js:474:19)
    at Socket.write (net.js:466:15)
    at [object Object].query (/home/hai/myapp/node_modules/pg/lib/connection.js:109:15)
    at [object Object].submit (/home/hai/myapp/node_modules/pg/lib/query.js:99:16)
    at [object Object]._pulseQueryQueue (/home/hai/myapp/node_modules/pg/lib/client.js:166:24)
    at [object Object].query (/home/hai/myapp/node_modules/pg/lib/client.js:193:8)
    at /home/hai/myapp/server.js:97:17
    at callbacks (/home/hai/myapp/node_modules/express/lib/router/index.js:160:37)
    at param (/home/hai/myapp/node_modules/express/lib/router/index.js:134:11)
    at pass (/home/hai/myapp/node_modules/express/lib/router/index.js:141:5)

Is there a way to keep the connection from timed out (better)? Or to reconnect on demand (best)? I have tried to redesign my app by not connecting to the database in the beginning, but upon the GET / request. This solution works only for the first request, then crashed on the second. Any insight is appreciated.

Answer 1

Have you looked into the postgres keepalive setting values? It sends packets to keep idle connections from timing out. http://www.postgresql.org/docs/9.1/static/runtime-config-connection.html

I also found this similar question: How to use tcp_keepalives settings in Postgresql?

You could also perform really minor queries from the db at a set interval. However, this method is definitely more hacked.

Edit: You could also try initiating the client like this:

var client = new pg.Client(conString);

Before you make your queries, you can check if the client is still connected. I believe you can use:

if(client.connection._events != null)
    client.connect();