Pooling: read ETIMEDOUT error after idle connection in Node 14

[AdlerJS]

This is one of my first issues on a node library so I'm going to do my best to explain and show my findings but if anymore information is needed please let me know. We recently upgraded to Node 14 as Node 12 was reaching end of life in azure services. Since then we have been getting random service crashes with the following error.

Error: read ETIMEDOUT
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:209:20)
    at TLSWrap.callbackTrampoline (internal/async_hooks.js:126:14)

I've looked at various other issues that people have reported and have implemented the keepAlive option and idleTimeoutMillis based on some findings and it did help make it more stable, however, after long periods of inactivity the crashes still occur. For reference we have a micro-service architecture with some services barely ever used as they are for one off integrations etc etc and not part of the main app. Our services share a 'database-connection' package in a lerna repo that handles the pooling of the connections. The code is as follows:

const { Pool } = require('pg');

const {
  POSTGRES_HOST,
  POSTGRES_PORT,
  POSTGRES_DATABASE,
  POSTGRES_USER,
  POSTGRES_PASSWORD,
  POSTGRES_MAX_CONNECTIONS,
  POSTGRES_CONNECTION_IDLE_TIMEOUT,
  POSTGRES_APPLICATION_NAME,
} = process.env;

const poolConfig = {
  host: POSTGRES_HOST,
  port: POSTGRES_PORT,
  database: POSTGRES_DATABASE,
  user: POSTGRES_USER,
  password: POSTGRES_PASSWORD,
  ssl: true,
  keepAlive: true,
  idleTimeoutMillis: 3000,
};

if (
  POSTGRES_MAX_CONNECTIONS &&
  !isNaN(Number.parseInt(POSTGRES_MAX_CONNECTIONS, 10))
) {
  poolConfig.max = Number.parseInt(POSTGRES_MAX_CONNECTIONS, 10);
}

if (
  POSTGRES_CONNECTION_IDLE_TIMEOUT &&
  !isNaN(Number.parseInt(POSTGRES_CONNECTION_IDLE_TIMEOUT, 10))
) {
  poolConfig.idleTimeoutMillis = Number.parseInt(
    POSTGRES_CONNECTION_IDLE_TIMEOUT,
    10
  );
}

if (POSTGRES_APPLICATION_NAME) {
  poolConfig.application_name = POSTGRES_APPLICATION_NAME;
}

let poolInstance = null;

const initializePool = () => {
  poolInstance = new Pool(poolConfig);
};

const pool = () => poolInstance;

module.exports = {
  initializePool,
  pool,
};

We then initialize our server on startup like

databaseConnection.initializePool();
const databaseConnectionPool = databaseConnection.pool();
databaseConnectionPool
  .connect()
  .then(() => (parseBoolean(process.env.RUN_MIGRATIONS)
    ? databaseMigrationUtility.executeMigrations(
      databaseConnection,
      'migrations',
      '../migration-files',
    )
    : Promise.resolve()))
  .then(() => {
    server.listen(process.env.PORT || 5010);
    monitoring.trackEvent('Started Service API');
  })
  .catch((error) => {
    logger.error(error);
  });

Queries to the database are made following the single query pattern from the docs.

const databaseConnection = require('database-connection'); // This is the index file of the pool connection above

const getExampleDataFromDatabase = (id1, id2) => {
  const query = `
    SELECT id1
    FROM TABLE_1
    WHERE id1 = $1
    AND id2  != $2`;
  return databaseConnection
    .pool()
    .query(query, [id1, id2])
    .then((result) => result.rows.map((row) => row.id));

After long periods of inactivity the service will crash with the error above. Is this expected behavior? Should we be handing connections differently? Let me know if you need anymore information

[charmander]

You need to attach an error listener to the pool. It doesn’t have to do anything (could just be poolInstance.on('error', console.error)).

The behavior is expected, but not good.

[AdlerJS]

@charmander - would adding a listener on the instance prevent the service from dying?

[charmander]

yes

[AdlerJS]

I'm going to make that change and run a service. If the service remains running for a day or so I will close this issue. Thanks!

[charmander]

Ah, also don’t do this and throw away the connection:

databaseConnectionPool
  .connect()

(connect() doesn’t initialize the pool, it’s how you request a client from the pool.)

[AdlerJS]

databaseConnection.initializePool();
databaseConnection.pool().connect()
	.then(() => (parseBoolean(process.env.RUN_MIGRATIONS)

Is the pattern we are using on service startup. We initialize the pool -> get the pool instance -> and then call connect. Is this not correct?

[charmander]

no

[sameekshamishra]

hi @danbunkr and @charmander,

I have a node js application running on azure as a webapp service. I'm experiencing container crash issue with the below error. The error does not explicitly point out to a line in the code or any library.

throw er; // Unhandled 'error' event
 ^
Error: read ETIMEDOUT
at TLSWrap.onStreamRead (internal/stream_base_commons.js:209:20)
Emitted 'error' event on TLSSocket instance at:
at emitErrorNT (internal/streams/destroy.js:106:8)
at emitErrorCloseNT (internal/streams/destroy.js:74:3)
at processTicksAndRejections (internal/process/task_queues.js:82:21) {
errno: -110,
code: 'ETIMEDOUT',
syscall: 'read'
}

Restarting the webapp in case of any issues is taken care by azure. However, the downtime incured due to this results into data loss.

Is there any update on the above thread? How can this be addressed and what causes this?

appreciate your response.

[otaviobertucini]

Seme error as @sameekshamishra

[costa-simulatedreality]

I am having the same problem here. Funny enough I only experience these errors on my production database server, but on the other environments (testing and staging). Can this be related to the postgres server configuration somehow?