Can't close postgreSQL connection in Node.js script
I am writing a small node.js server to support supporting machines. Mostly for testers, it is possible to remotely delete the db or reboot the server. I have some problems with pg connections. Can anyone understand why it doesn't close after the 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 the screen after the first request is:
drop-db
connect
callback
close
fine, but after next request I only get
drop-db
connect
after the next one i already get pg error
what am I doing wrong?
Edit: There are no errors after the 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 has to do with opening a new connection while the old one is still on.
Edit 2:
I checked the postgres logs:
after the second request:
2012-03-13 09:23:22 EET LOG: invalid length of startup packet
after the third request:
2012-03-13 09:24:48 EET FATAL: invalid frontend message type 0
source to share
It looks like the client (pg.Client) is declared outside the scope of the request, this is probably your problem. It's hard to tell from the code snippet, but it looks like you might have problems defining the scope and how the asynchronous callback control flow works, for example. calling res.end () while the callbacks are still on the I / O queue. This is completely legal with node, just not sure if this is your intent.
It is preferable to use pg.connect, which returns the 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);
source to share
I was getting this error similar to you and it was that the connection was not closed. When you try to (re) connect through an already open connection, things go boom. I would suggest that you are using direct join material, since you don't seem to need the join code, might ease the problem. (Although, given that I have resurrected an older post, I suspect you have probably already fixed this.)
source to share