5

I have a nodeJS(v.0.10.23) proxy connecting to a postgres db (node-postgres module v2.1.0), along with pgpool-II which returns all sorts of json data.

Back in the day, this is how connection errors were handled:

var after = function(callback) {
    return function(err, queryResult) {
        if(err) {
            response.writeHead(500, _header);
            console.log("ERROR: 500");
            console.log(err);
            return response.end(JSON.stringify({error: err}));
        }
        callback(queryResult)
    }
};

Basically what it does, is consuming the response if no errors are present.

An in depth explanation can be found here: Node js - http.request() problems with connection pooling

Using the function above, i got something like this:

pg.connect(_conString, after(function(err, client, done) {
  client.query(sql, after(function(result) {
  ...
  done();
}

Since the context is lost when the function is passed into after()s callback, i'm loosing the ability to use the innate done() method passed by pg.connect().

Removing the after solves the issue, but then, in due time and with a fair amount of clients pulling the data, the node will hang until it is reset.

Is there a different way of consuming various asynchronous responses?

or perhaps a way to pass the pg.connect context into the callback?

Community
  • 1
  • 1
silicakes
  • 6,364
  • 3
  • 28
  • 39
  • please give more of your code and describe what you are tring to accomplish – Shimon Doodkin Jan 23 '14 at 14:05
  • the other parts of the code are mainly some toJSON parsing logic, it shouldn't be relevant. The node suddenly hangs indefinitely, it doesn't crash but doesn't respond either. After we reset it, we see all the "hanged" requests being called at once. – silicakes Jan 23 '14 at 14:21
  • @Mike86 - were you able to make nodejs to talk to pgpool (via the pg module)? I'm struggling with that, can you tell me which pg-pool version you used? – Yaron Naveh Mar 25 '14 at 14:34
  • in particular did you use parameterized queries which seem to be an issue for me – Yaron Naveh Mar 25 '14 at 16:37
  • I sure have; all you need to do is set up the pool and point your njs connection string to it. What i'm doing is passing my nodeJS instance an AJAX get with some object containing properties which later own are mapped to the current stored procedures i want, along with their corresponding parameters. The query is then built accordingly and passed through pg.connect( to the appropriate tcp://pgdburl:port/ which is being mapped to a pgpool instance configured to pass the request to the appropriate db – silicakes Mar 25 '14 at 18:50
  • Thanks @Mike86 ! Just to clarify: do you create a query sql as string yourself and send it, or do you use the 'pg' library parameterized query syntax: c.query(sql, params, cbx)? The latter is the one that makes me problems with pgpool. The former works but is open to sql injection and performance issues. – Yaron Naveh Mar 25 '14 at 19:38
  • I use the c.query variation, however i can't recollect whether i ignore the params bit and just stuff everything into the sql string, or if i actually use it, ping me in like 14 hours and I'll let you know. – silicakes Mar 25 '14 at 19:42
  • Hi @Mike86 - pinging you... let me know if you got parameterized queries working with node and pgpool – Yaron Naveh Mar 26 '14 at 07:56
  • BTW pgpool 3.3.1 seem to work well, doing more testing. Would still appreciate if you can check on your side. – Yaron Naveh Mar 26 '14 at 08:54
  • So what i'm doing is building the complete sql query inside the sql variable, including the params, and just passing it as: client.query(sql, callback); That does the trick alright. Think we're also on 3.3.1 – silicakes Mar 26 '14 at 11:35

3 Answers3

2

Well, of course you're losing done(), you never pass a third argument to your callback in your after() function.

function after(cb) {
    return function() {
        // you're using this function in a context where the arguments
        // passed in could be anything. the only constant is that the first
        // argument is the error, if any
        if (arguments[0]) {
            response.writeHead(500, _header);
            console.log("ERROR: 500");
            console.log(err);
            return response.end(JSON.stringify({error: arguments[0]}));
        }
        // apply the entire argument list to the callback, without modification
        cb.apply(cb, arguments);
    };
}

... this also fixes the dubious convention of passing client through the queryResult variable.

Jason
  • 13,606
  • 2
  • 29
  • 40
0

see the last example (using factory functions and passing response) it is quite fun to use factory functions because it saves code

app.get('/parallel',function(request,response,next){  

function err(err)
{
  console.log(err.stack);
  response.end('error'+err.stack);
}
function send(data)
{
  response.end(JSON.stringify(data))
}

pg.connect(config, function(err, client, done) {
  if(err){done();return err(err);}
  client.query("SELECT * FROM NOW()", function(err, result) {
      done();
      if(err){return err (err);}
      send(result);
    }, 0)
  })
})

you can try using colan's async to to the selects.

npm install async

// an example using an object instead of an array

var x1=null;
async.series({// in javascript the creation order is preserved in Object.keys
one: function(callback){
    setTimeout(function(){
        x1=3;
        callback(null, 1);
    }, 200);
},
two: function(callback){
    console.log('select where x1='+x1);
    setTimeout(function(){
        callback(null, 2);
    }, 100);
}
},
function(err, results) {// called instantly if there is an error
// results is now equal to: {one: 1, two: 2}
});

single select factory the callbacks

function makesenderr(response){
 return function senderr(err,ok)
 {
  console.log(err.stack);
  response.end('error'+err.stack);
 }
}

function makesendjson(response){
 return function sendjson(data)
 {
  response.end(JSON.stringify(data))
 }
}


function tryconn(err,ok){
  return function(errarg, client, done) {
   if(errarg){done();return err(errarg);}
   ok(client,done);
  }
}


function doneerrok(done,err,ok){
  return function(errarg, result) {
   done();
   if(errarg){return err(errarg);}
   ok(result);
  }
}

var async=require('async')

app.get('/foo',function(request,response,next){
 var senderr=makesenderr(response)
 var sendjson=makesendjson(response)    
 pg.connect(config, tryconn(senderr,function(client,done){
      client.query("SELECT one FROM t1",doneerrok(done,senderror,sendjson), 0)
 }))
})

using factory functions and passing response

 function senderr(response,err)
 {//add headers here
  console.log(err.stack);
  response.end('error'+err.stack);
 }


 function sendjson(response,data)
 {//add headers here
  response.end(JSON.stringify(data))
 }


function tryconn(response,ok){
  return function(err, client, done) {
   if(err){done();return senderr(response,err);}
   ok(client,done);
  }
}

function donerespok(done,response,ok){
  return function(err, result) {
   done();
   if(err){return err(response,err);}
   ok(response,result);
  }
}

function respok(response,ok){
  return function(err, result) {
   done();
   if(err){return err(response,err);}
   ok(response,result);
  }
}

function donecb(done,cb){ return function(){done();cb.apply(this,arguments);} }

var async=require('async')

app.get('/foo',function(request,response,next){  
 pg.connect(config, tryconn(response,function(client,done){
      client.query("SELECT one FROM t1",donerespok(done,response,sendjson), 0)
 }))
})

app.get('/series',function(request,response,next){  
 pg.connect(config, tryconn(response,function(client,done){

    var one={};
    async.series({ 
        one: function(cb){ 
                  client.query("SELECT one FROM t1", function(err, result) {
                   one=result;cb(err,result);
                  }, 0)
             },
        two: function(cb){
                  client.query("SELECT two FROM t2 where one="+one[0].one,cb, 0)
        }
    },  // results is now equal to: {one: [{one:1}], two: [{two:2},{two:2}]}
    donerespok(done,response,sendjson) );

 }))
})

app.get('/parallel',function(request,response,next){  

    async.parallel({ 
        one: function(cb){ 
         pg.connect(config, tryconn(response,function(client,done){
              client.query("SELECT one FROM t1",donecb(done,cb), 0)
         }))
        },
        two: function(cb){
         pg.connect(config, tryconn(response,function(client,done){
              client.query("SELECT two FROM t2",donecb(done,cb), 0)
         }))
        }
    },  // results is now equal to: {one: [{one:1},{one:2}], two: [{two:1},{two:2}]}
    respok(response,sendjson) );
})
Shimon Doodkin
  • 4,310
  • 34
  • 37
0

First off, you should increase your connection pool if you haven't already, this looks like something you don't want limited to 6 connections. You should also probably set a fairly low timeout on the requests.

As for the context of the data, have you considered binding pg.connect's this to the after function? This would allow you to have access to done in the local context.

pg.connect(_conString, after(function(err, client, done) {
  this.done = done;
  client.query(sql, (after(function(result) {
     ...
      this.done();
  }).bind(this));
});

Smashing magazine had a good article on using bind() a few days ago here

jeremy
  • 4,294
  • 3
  • 22
  • 36
  • You bound `this` to `after()`, but you still haven't set anything to `done`... it's empty. – Jason Jan 29 '14 at 21:50
  • Your right, missed that. I think overall there is a better way to achieve what he is looking for. I'm not real sure where @Mike86 thinks the `done` function is being passed in. Perhaps he meant `pg.connect(_conString, done, after...`. – jeremy Jan 29 '14 at 22:26
  • Actually @jeremy's got a point, however for the time being, i'm not quite certain it will resolve the pool. I fear that up until this moment we didn't have a proper testing environment so that i couldn't really dive into that during this week. If the bounty fails, I'll set another one during the week i'll run my tests so your help won't go in vain. P.S our pool is 32x4 (32 connections per core) which imo should be more than sufficient for our current user volume and its concurrency – silicakes Jan 30 '14 at 08:59
  • `done()` is passed to the callback by the pg.connect method, it's just that the `after()` function ignores it, hence the updated `after()` function in my answer. – Jason Jan 30 '14 at 15:24