Abort connections/queries on close

[unilynx]

This helps ensure that connections are closed and queries rejected when the server goes away for whatever reason.

(originally written by @blexrob)

We've been running this change for a couple of weeks/months without problems.

We've got some test cases but these are not trivial to port back to postgrejs. See this as pseudo-code...

async function testClosedConnectionHandling() {
  const worker = new AsyncWorker;
  await worker.callRemote("@mod-webhare_testsuite/tests/system/nodejs/data/context-tests.ts#runShortLivedContext", 0);
  await worker.callRemote("@mod-webhare_testsuite/tests/system/nodejs/data/context-tests.ts#runShortLivedContext", 1);
  await worker.callRemote("@mod-webhare_testsuite/tests/system/nodejs/data/context-tests.ts#testQueryInNewContext");

  //test with an external kill to the postgres process to see if we catch serverside disconnects to
  const worker2 = new AsyncWorker;
  await worker2.callRemote("@mod-webhare_testsuite/tests/system/nodejs/data/context-tests.ts#runAndKillTransaction");
  await worker2.callRemote("@mod-webhare_testsuite/tests/system/nodejs/data/context-tests.ts#testQueryInNewContext");
}

export async function runShortLivedContext(sleepMs: number) {
  const context = new CodeContext("shortContext 1");

  await context.run(async () => {
    db();
    // Wait for the db connection to become ready, after that the blobtype oid scan is started
    await new Promise(resolve => __getConnection()["pgclient"]?.on("ready", resolve));
    /* exit - this will kill the db connection. If queries aren't correctly closed when the context is
       closed, the blob oid scan will hang indefinately and subsequent connections will hang on it
    */
    if (sleepMs) {
      await sleep(sleepMs);
    }
  });
  context.close();
}

export async function testQueryInNewContext() {
  const context = new CodeContext("shortContext 2");
  try {
    return await context.run(async () => {
      // Initialize db connection. will start scanning the blob oid.
      return sql`SELECT 1 AS result`.execute(db());
      // exit - this will kill the db connection
    });
  } finally {
    context.close();
  }
}

export async function runAndKillTransaction() {
  await beginWork(); //work is needed to prevent crash but also makes us safer if we start to pool
  const pid = (await query<{ pg_backend_pid: number }>('select pg_backend_pid()')).rows[0].pg_backend_pid;
  const promised = query('select pg_sleep(10)');
  promised.catch(() => { });
  await sleep(100); //give the PG driver time to start the query
  process.kill(pid, 'SIGTERM');
  await test.throws(/Connection closed/, promised);
}

[erayhanoglu]

Thank you for contributing. This is a great patch.