KnexTimeout connection Issue while connecting with PostgreSQL DB

[sharmayash02]

Environment

Knex + Postgres + Node version:

"knex": "^3.1.0"
"pg": "^8.11.3"
"node": "20.11.0"

@lorefnon @smorey2

Bug

1. Issue Observed:

On running longevity, we are getting Knex timeout issue for PostgreSQL connection. This issue is intermittent.
Use-case: We need to upsert (insert/Modify) a table and insert the operation into audit table with the type of operation performed. for example: if insert operation performed then "insert" as operation string gets inserted in the audit table.
There is a transaction in query with async-await approach.

2. Error message:

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?","stack":"KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?\n at Client_PG.acquireConnection (/app/node_modules/knex/lib/client.js:332:26)\n at async Runner.ensureConnection (/app/node_modules/knex/lib/execution/runner.js:305:28)\n at async Runner.run (/app/node_modules/knex/lib/execution/runner.js:30:19)

3. Test code:

Database connection

dbconnection = knexfactory({
        client: 'pg',
        connection: connectionConfig,
        pool: {
          max: 3,
        },
        migrations: {
          directory: join(__dirname, './db/scripts'),
          tableName: 'migrations',
        }
      });

Code contain Database Query

async afun(){
    try {
        const instanceData = { q_id: "qid", t_id: 1, value: JSON.stringify({ "key": "value" }) };
        const databaseConnection = this.appDatabase.getDbConnection(tenantId);
        const queryResult = await databaseConnection.transaction(async (trx) => {
            return await trx
                .with(
                    'updated',
                    trx
                        .from(DatabaseConstants.TNSI)
                        .where(DatabaseConstants.CNQID, instanceData.q_id)
                        .andWhere(DatabaseConstants.CNTID, instanceData.t_id)
                        .update(instanceData)
                        .returning(`*`)
                )
                .with(
                    'inserted',
                    trx
                        .from(DatabaseConstants.TNSI)
                        .insert(instanceData)
                        .onConflict([DatabaseConstants.CNQID, DatabaseConstants.CNTID])
                        .ignore()
                        .select(DatabaseConstants.CNQID, DatabaseConstants.CNTID, DatabaseConstants.CNV)
                        .whereNotExists(trx.select(1).from(`updated`))
                        .returning(`*`)
                )
                .with(
                    `result`,
                    trx
                        .from(`updated`)
                        .columns(
                            { [DatabaseConstants.CNW]: trx.raw(`?`, [userId]) },
                            { [DatabaseConstants.CNWH]: trx.raw(`row_to_json(updated.*)`) },
                            { [DatabaseConstants.CNOP]: trx.raw(`?`, `update`) }
                        )
                        .select()
                        .unionAll(
                            trx
                                .from(`inserted`)
                                .columns(
                                    { [DatabaseConstants.CNW]: trx.raw(`?`, [userId]) },
                                    { [DatabaseConstants.CNWH]: trx.raw(`row_to_json(inserted.*)`) },
                                    { [DatabaseConstants.CNOP]: trx.raw(`?`, `insert`) }
                                )
                                .select()
                        )
                )
                .insert(
                    trx
                        .from<IDbSettingsInstances>(`result`)
                        .select(`*`)
                )
                .into(trx.raw(`?? (??, ??, ??)`, [DatabaseConstants.TNSA, DatabaseConstants.CNW, DatabaseConstants.CNWH, DatabaseConstants.CNOP]))
                .returning(DatabaseConstants.CNWH);
        });
        return { result: queryResult };
    } catch (error) {
        if (error && error.code == `23503`)
            return { errCode: ErrorCodeConstants.TYPE_ID_NOT_PRESENT };
        throw error;
    }
}

Questions

• Does it needs to have "transacting" method?
• If not, then why KnexTimeout issue is occuring?