1

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.

Hai Vu
  • 37,849
  • 11
  • 66
  • 93

4 Answers4

2

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();
Community
  • 1
  • 1
sheldonk
  • 2,604
  • 3
  • 22
  • 32
2

faced the same problem.. telling the client to close connection upon the end event

query.on('end', function() { 
  client.end();
});

did the trick for me...

Wisu Suntoyo
  • 158
  • 1
  • 2
  • 7
  • 1
    It took sometime, but I finally learned to do this. Basically, for every request, there must be a new connection, processing, then closing. – Hai Vu Apr 24 '13 at 13:13
  • Would it also work to simply do `query.on('end', client.end)`? – Sebastian Mar 14 '21 at 14:37
1

I'm using the parameter keepAlive in true and it works.

This is my configuration and it is solved.

const client_pg = new Client({
    connectionString,
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000
  });
Elias Vargas
  • 1,126
  • 1
  • 18
  • 32
0

You can also change the default idle timeout of 30 seconds to whatever value you need. E.g.

pg.defaults.poolIdleTimeout = 600000; // 10 mins
DeadPassive
  • 877
  • 3
  • 8
  • 22