Connection to postgres closed with 'This socket is closed' error message
I'm migrating toward node.js 0.6.12 and now got the following error messages when using pg module (version 0.6.14):
Error: This socket is closed. at Socket._write (net.js:453:28) at Socket.write (net.js:446:15) at [object Object]._send (/home/luc/node_modules/pg/lib/connection.js:102:24) at [object Object].flush (/home/luc/node_modules/pg/lib/connection.js:192:8) at [object Object].getRows (/home/luc/node_modules/pg/lib/query.js:112:14) at [object Object].prepare (/home/luc/node_modules/pg/lib/query.js:150:8) at [object Object].submit (/home/luc/node_modules/pg/lib/query.js:97:10) at [object Object]._pulseQueryQueue (/home/luc/node_modules/pg/lib/client.js:166:24) at [object Object].query (/home/luc/node_modules/pg/lib/client.js:193:8) at /home/luc/test/routes/user.js:23:29
The line indicated in my code is:
var get_obj = client.query("SELECT id FROM users WHERE name = $1", [name]);
This use to work fine with node 0.4.8 and gp 0.5.0 but does not work anymore now I'm testing the migration.
I saw several error like this one on the net but no answer.
This seems to be linked to the way I handle my postgres Connection. Today I create a single connection when running the app. I think creating a new connection on each request would be better. Is the best solution to have the connection created in an express middleware ?
Normally, frameworks and middleware keep the connection open (or: a pool of connections). The problem lies most probably in your node.js code (or usage). BTW: if you have access to the postgres's logfiles, you can probably see explicit disconnections from the node.js. (log_connections and log_disconnections should both be set to True to see this)
Connect+disconnect is considered an expensive operation (TCP traffic, authorisation, forking a worker process (for postgres) , session setup, logging, (accounting?) ). But if it works for you (or you have only one request+reply for the session) it's okay.
Cost /resource usage estimates:
For the session setup:
- TCP/IP connection setup: 2*2 IP packets := 4*round-trip delay
- login /password:
- 2*2 TCP readwrites := 4 * round-trip delays
- 4 system R/W calls
- a few database queries / lookups for user authorisation, (say 10...100 disk reads; mostly cached)
- session construction := fork (for postgres) + lots of COW pages being cloned (? 100-1000 pagefaults?)
- session initialisation := a few round trips
for the query:
- send+ receive query := a few TCP/IP round-trips
- parse := a few (1...100) catalog lookups (mostly from disk cache)
- execute := xxx disk reads (possibly from cache)
- fetch and store results := allocate (dirty) buffers
- send results := xxx TCP round-trips
- discard result-buffers := (almost for free!)
- 3*2 IP roundtrips
- exit() of the child process, wait() for the parent process (Sorry, I think in unix-terms ;-)
- 1 socket-descriptor in TIME_WAIT state for a few seconds / minutes
As you can see, the amount of resources spent on connection build-up is 10, maybe 100 times as big as what a typical query+result will cost; if you have more than one query to execute it will be wise to keep the connection open. (or maintain a pool of open connections)
For simplicity, I ignored CPU consumption and mainly ignored memory/buffer usage. Nowadays, CPU almost seems for free; the amount of calculation that can be done while waiting for a disk (10 ms) or network (x ms) is incredible: several (100...10K?) ticks per byte.