CODEWITHSUNDEEP
javascriptnode.jspostgresqlnpmpg
Frederic Le Feurmou
Frederic Le Feurmou

Reputation: 1786

Manage many connections from nodejs to postgres with pg

I want to execute a script that will perform a lot of queries and I use pg to manage the connections. The problem is that when my pool is full, my program stops and doesn't queue the future queries. I try to set the poolSize at 0 but it doesn't work either.

pg.defaults.poolSize = 100000000;

var pgQuery = function(query) {
  return new Promise(function(resolve, reject) {
    pg.connect(conString, function(err, client) { // When the pool is full, pg doesn't connect to the DB
      if(err) {
        console.error('error fetching client from pool', err);
        reject(err);
      }
      client.query(query, 
      function(err, res) {
        if(err) {
          console.error('error running query', err);
          reject(err);
        }
        resolve(res);
      });
    });
  });
};

Any suggestions?

Upvotes: 1

Views: 2242

Answers (1)

Frederic Le Feurmou
Frederic Le Feurmou

Reputation: 1786

I found my problem, I wasn't releasing the clients back to the pool. For this I had to call the done() callback after my query was executed.

var pgQuery = function(query) {
  var attempt = 0;
  return new Promise(function(resolve, reject) {


    // I added the done callback in parameter.
    pg.connect(conString, function(err, client, done) {
      if(err) {
        console.error('error fetching client from pool', err);
        attempt++;
        if (attempt < 11) {
          pgQuery(query);
        } else {
          reject(err);
        }
      }
      client.query(query, 
      function(err, res) {


        //call `done()` to release the client back to the pool
        done();
        if(err) {
          console.error('error running query', err);
          reject(err);
        }
        resolve(res);
      });
    });
  });
};

Also my request is cpu intensive and takes a long time to execute. Because of that my request was called twice, I fixed this with 

// Without this line the http request is triggered twice
req.connection.setTimeout(15*60*1000)

Upvotes: 2

Related Questions