Add ``executionTimeout`` / ``maxExecutionTimeout`` / ``maxExecutionTime`` handling

[dschlarmann]

In order to handle slow / too complex queries, which might

• be too slow for the actual users
• slow down the whole service

... we should add an executionTimeout / maxExecutionTimeout / maxExecutionTime (or however the corresponding property is defined in the nestjs/typeorm cosmos).

Requirements

1. Requests/Operations can be aborted after a period of time
2. Per default, all Database queries should be cancled afer a period of time.

Approach - Requirement 1

Service Side (typeORM)

It seems that typeORM does not offer any feature to cancel a datasource.query function after a period of time. Check the following:

• https://stackoverflow.com/questions/70789249/can-i-abort-db-operation-transaction-in-typeorm-using-abortsignal
• https://github.com/typeorm/typeorm/issues/8552

If we would use the datasource.createQueryBuilder() with the repository entity approach, we would have been able to use the maxExecutionTime function (

• https://www.kindacode.com/snippet/typeorm-how-to-limit-query-execution-time/
• https://typeorm.io/select-query-builder#max-execution-time
• https://github.com/typeorm/typeorm/issues/9214#issuecomment-1188426399

BUT, we don't. Instead we're using datasource.query() were those params can not be passed.

Service Side (Database)

Also aborting an SELECT Statement on the database itself, is not possible. Timeouts are only definable on Schema, User or Connection side.

• https://navicat.com/en/company/aboutus/blog/2237-setting-query-timeouts-in-postgresql
  • In professional grade databases such as PostgreSQL, there are settings to cap query execution time for the entire database or even per user, via the statement_timeout variable
• https://dba.stackexchange.com/questions/290714/how-to-set-statement-timeout-per-user

For this reason we should add, if possible, a custom behavior for canceling the query-promise after a period of time. Although this would only cancel the actual promise and not the database query, the user would have control over the waiting time. Implementing a custom Abort-Signal would be needed for this Promise-Approach. Cf. the following exmaple how this is done using fetch(): https://simonplend.com/automatically-cancel-async-operations-with-abortsignal-timeout/ In addition, we should add a default timeout --> cf. next paragraph.

Approach - Requirement 2

Timeout params can be set using the extra-properties in the TypeOrmModule initialization.

TypeOrmModule.forRoot({
      type: process.env.db_postgres_type,
      host: process.env.db_postgres_host,
      port: process.env.db_postgres_port,
      username: process.env.db_postgres_username,
      password: process.env.db_postgres_password,
      database: process.env.db_postgres_database,
      synchronize: JSON.parse(process.env.db_postgres_synchronize),
      logging: JSON.parse(process.env.db_postgres_logging),
      subscribers: [],
      migrations: [],
      extra: {
        query_timeout: 100,
        statement_timeout: 100,
      },
    }

Available properties: https://node-postgres.com/apis/client#new-client For now I'm not 100% sure what values might be the best. Some more information:

• https://stackoverflow.com/a/59393425/2873979
• https://stackoverflow.com/questions/63837096/how-to-add-a-request-timeout-in-typeorm-typescript

Maybe we could start with 30000 (15sec) ? By setting these params will ensure that queries will terminate after a specific period of time.

In addition, we also should add the handling for the initial connection timeout, which can be set using the connectTimeoutMS param (cf. https://typeorm.io/data-source-options#postgres--cockroachdb-data-source-options).