Can not close postgreSQL connection within Node.js script

Question

I am writing small node.js server for helping maintaining build machines. It's basically for testers to be able to drop db or restart server remotely. I have some issues with pg connections. Can anybody have an idea why it is not being closed after first request?

var client = new pg.Client(conString);

var server = http.createServer(function (req, res) {
var url = parse(req.url);

if (url.pathname =='/'){
    (...)
}else{
     var slash_index = url.pathname.indexOf('/',1);
     var command = url.pathname.slice(1,slash_index);

     if (command =='restart'){
        res.write('restarting server please wait');
     } else if (command == 'drop-db'){

     console.log('drop-db');
     client.connect();
     console.log('connect');
     var query = client.query("select datname from pg_database;", function(err, result) {
        if (err) throw err;
        console.log('callback');
        });

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

} else{
  res.write('unknown command : '+ command);
}
res.write('\n');
res.end();
}


}).listen(5337);

So what I get on screen after first request is :

drop-db
connect
callback
close

great but after next request I get only

drop-db
connect

after next one I already get an pg error

what do I do wrong?

Edit : No errors after second commit . Error after third :

events.js:48
    throw arguments[1]; // Unhandled 'error' event
                   ^
error: invalid frontend message type 0
    at [object Object].<anonymous> (/home/wonglik/workspace/server.js/node_modules/pg/lib/connection.js:412:11)
    at [object Object].parseMessage (/home/wonglik/workspace/server.js/node_modules/pg/lib/connection.js:287:17)
    at Socket.<anonymous> (/home/wonglik/workspace/server.js/node_modules/pg/lib/connection.js:45:22)
    at Socket.emit (events.js:88:20)
    at TCP.onread (net.js:347:14)

I think it is related to opening new connection while old is still on.

Edit 2 :

I've checked postgres logs :

after second request :

 2012-03-13 09:23:22 EET LOG:  invalid length of startup packet

after third request :

 2012-03-13 09:24:48 EET FATAL:  invalid frontend message type 0

Answer 1

It looks like client (pg.Client) is declared outside the scope of a request, this is probably your issue. It's hard to tell from the code snippet, but it looks like you might have issues with scoping and how async callback control flow works in general, e.g. calling res.end() while callbacks are still in the IO queue. This is totally legal with node, just not sure that is your intent.

It is preferred to use pg.connect which returns a client. see https://github.com/brianc/node-postgres/wiki/pg

var pg = require('pg');

var server = http.createServer(function (req, res) {
  var url = parse(req.url);

  if (url.pathname =='/'){
      (...)
  }else{
       var slash_index = url.pathname.indexOf('/',1);
       var command = url.pathname.slice(1,slash_index);

       if (command =='restart'){
          res.write('restarting server please wait');
       } else if (command == 'drop-db'){

       console.log('drop-db');
       pg.connect(conString, function(err, client) {
         console.log('connect');
         var query = client.query("select datname from pg_database;", function(err, result) {
            if (err) throw err;
            console.log('callback');
            });

         query.on('end', function() {
                 console.log('close');
                 // client.end(); -- not needed, client will return to the pool on drain
                 });

       });

  } else{
    res.write('unknown command : '+ command);
  }
  // these shouldn't be here either if you plan to write to res from within the pg 
  // callback
  res.write('\n');
  res.end();
}


}).listen(5337);