Allow setting global SQL statement timeout for registry API

[t83714]

Allow setting global SQL statement timeout for registry API

We currently don't allow setting global SQL statement timeout for registry API via Helm chart config.

Adding this config to ensure users have more control over the potential impact of the long queries.

Solution

• Setting Postgresql Connection config statement_timeout
• ~The setting can be passed via JDBC connection string.~ doesn't work -- see comment below
  • e.g. jdbc:postgresql://localhost:5432/postgres?options=-c%20search_path=test,public,pg_catalog%20-c%20statement_timeout=90000
  • see here: https://jdbc.postgresql.org/documentation/use/#connection-parameters
• Session scope timeout can be set via session.queryTimeout(seconds: Int)

[t83714]

options in JDBC connection string won't be applied by the connection pool implementation of ScalikeJDBC JDBC.

Eventually, implement our own pool factory over default commons-dbcp2 pool and leverage its connectionInitSql config.

Side-effective, the db.default.poolFactoryName config must stay at value magda (use MagdaDBConnectionPoolFactory) to make the solution work.

[t83714]

closed via PR: https://github.com/magda-io/magda/pull/3468