pg version 8.7.1 hangs on await db.end() but before version 8 doesn't

[Animadei]

The node-postgres module frequently hangs when the PostgreSQL server restarts abruptly. The node-postgres package was working fine prior to pg version 8.7.1 as required by Node.js version 16. The two positions marked A and B represents when a disconnect from the database could be experienced. The db.end() API hangs on the await in the bad case. Commenting out the await avoids the hang.

let db = null;

try {
  db = new pg.Client({...});
  await db.connect();
  // Position A
  await db.query("<SQL query>");
  // Position B
} finally {
  if (db) {
//    await db.end(); // the await can hang indefinitely
    db.end(); // workaround fix
  }
}

The Client.end() routine has a new implementation. Could someone check the logic to guarantee the Client.end() returned promise always resolves.

The two related issues are:

1. Connection hang on the server side after ended #2329
2. Bug in pool.end () never returns when trying to close cleanly #2341

[alfreema]

I was having this issue when using jest to do some integration testing. That lead down a rabbit-hole that solved a bunch of problems including the ability to get pool.end() working properly.

By treating "new Pool()" as a promise, it cleaned my whole mess up:

async function createPool () {
  return await new Pool({ connectionString })
}

and all of the issues with closing clients, pools, etc completely went away just by awaiting "new Pool(...)". Maybe new Pool needs a Process.nextTick( .. ) somewhere? I'm clueless, but hopefully this is a clue to help the brainiacs figure out the underlying problem.

[ws02589111]

Got the same issue with query, the code is hang forever

return Promise.fromCallback((callback) => {
     getPool(db).query(sql, params, callback);
})

Node: v16.13.0
pg: 8.7.1

The pg with node 12.9.1 is work fine

[alfreema]

Got the same issue with query, the code is hang forever

I'm curious if you tried the workaround above, and throwing "await" in front of your initialization?

[snowbldr]

@alfreema I had this same issue. I tried adding await to the new Pool call, and can confirm it did fix it for me. Thanks a bunch for the work around 👍

This happened with me in 8.7.3, this did not work with me, tried this and it worked perfectly

const postgresPool = new Pool({ connectionString })
const client = await postgresPool.connect()
await client.release()
await postgresPool.end()

basically just fetch a "client" from the pool and release it before ending the connection.

[Animadei]

This happened with me in 8.7.3, this did not work with me, tried this and it worked perfectly

const postgresPool = new Pool({ connectionString })
const client = await postgresPool.connect()
await client.release()
await postgresPool.end()

basically just fetch a "client" from the pool and release it before ending the connection.

The reason why your solution "seems to work" is because you never actually ended the client session and released it back to the running pool. The pgbouncer connection pooler is a more efficient connection pooler that serves multiple applications rather than tying up several connections per node process. Will wait for an official solution.

[charmander]

The reason why your solution "seems to work" is because you never actually ended the client session and released it back to the running pool.

@Animadei client.release() releases the client back to the running pool and postgresPool.end() ends the client session, no? It’s someone who fixed their pool.end() hang caused by misuse of the API, and unrelated to this issue.

[Animadei]

The reason why your solution "seems to work" is because you never actually ended the client session and released it back to the running pool.

@Animadei client.release() releases the client back to the running pool and postgresPool.end() ends the client session, no? It’s someone who fixed their pool.end() hang caused by misuse of the API, and unrelated to this issue.

I agree, technically the hack could work, and if it does, we could isolate the issue.