How to handle idle_in_transaction_session_timeout?

Question

When we set idle_in_transaction_session_timeout, the database will terminate connections that are idle for some time.
This works as expected, but I wonder how we should deal with this situation in the aplication code.
We are using pg-promise 10.3.1.

Details of the test:

• we set the connection pool size to 1, so that we only have a single session
• we set the for the idle-transaction-session-timeout to 2.5 sec:
  SET idle_in_transaction_session_timeout TO 2500
  • now the active transaction will be in state idle in transaction:
    see What can cause “idle in transaction” for “BEGIN” statements
• now we start a transaction and sleep for 5 seconds
• after 2.5sec the database will terminate the session and send an error to the client:
  pgp-error error: terminating connection due to idle-in-transaction timeout
• after another 2.5sec the transactional code tries to send a query (via the already terminated session), and this fails as expected:
  dbIdle failed Error: Client has encountered a connection error and is not queryable
  • then pg-promise will try to rollback the transaction which will also fail (also expected, I guess)
• But now we start a new query and also this query fails with
  dbCall failed Client has encountered a connection error and is not queryable
  • is this expected? I was hoping that pg-promise can somehow remove the "broken" connection from the pool and that we could get a new one
  • obvously this is not the case, so how should we deal with this situation: i.e. how to recover, so that we can send new queries to the database?

Code example:

import pgPromise, { IMain } from "pg-promise";
import * as dbConfig from "./db-config.json";
import { IConnectionParameters } from "pg-promise/typescript/pg-subset";

const cll = "pg";
console.time(cll);

const pgp: IMain = pgPromise({
  query(e) {
    console.timeLog(cll,`> ${e.query}`);
  },
  error(e, ctx) {
    console.timeLog(cll,"pgp-error", e);
  }
});

const connectParams: IConnectionParameters = {
  ...dbConfig,
  application_name: "pg-test",
  max: 1
};
const db = pgp(connectParams);

/**
 * @param timeoutMs 0 is no timeout
 */
async function setDbIdleInTxTimeout(timeoutMs: number = 0) {
  await db.any("SET idle_in_transaction_session_timeout TO $1;", timeoutMs);
}

async function dbIdle(sleepTimeSec: number) {
  console.timeLog(cll, `starting db idle ${sleepTimeSec}`);
  const result = await db.tx(async t => {
    await new Promise(resolve => setTimeout(resolve, sleepTimeSec * 1000));
    return t.one("Select $1 as sleep_sec", sleepTimeSec);
  });
  console.timeLog(cll, result);
}

async function main() {
  await setDbIdleInTxTimeout(2500);
  try {
    await dbIdle(5);
  } catch (e) {
    console.timeLog(cll, "dbIdle failed", e);
  }
  try {
    await db.one("Select 1+1 as res");
  } catch (e) {
    console.timeLog(cll, "dbCall failed", e);
  }
}

main().finally(() => {
  pgp.end();
});

Console output (removed some useless lines):

"C:\Program Files\nodejs\node.exe" D:\dev_no_backup\pg-promise-tx\dist\index.js
pg: 23.959ms > SET idle_in_transaction_session_timeout TO 2500;
pg: 28.696ms starting db idle 5
pg: 29.705ms > begin
pg: 2531.247ms pgp-error error: terminating connection due to idle-in-transaction timeout
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23) {
  name: 'error',
  severity: 'FATAL',
  code: '25P03',
}
pg: 2533.569ms pgp-error Error: Connection terminated unexpectedly
pg: 5031.091ms > Select 5 as sleep_sec
pg: 5031.323ms pgp-error Error: Client has encountered a connection error and is not queryable
pg: 5031.489ms > rollback
pg: 5031.570ms pgp-error Error: Client has encountered a connection error and is not queryable
pg: 5031.953ms dbIdle failed Error: Client has encountered a connection error and is not queryable
pg: 5032.094ms > Select 1+1 as res
pg: 5032.164ms pgp-error Error: Client has encountered a connection error and is not queryable
pg: 5032.303ms dbCall failed Error: Client has encountered a connection error and is not queryable

Process finished with exit code 0

Answer 1

This issue #680 has been fixed in pg-promise 10.3.5