Temporary table can't be created in read-only DB. Changes in #353 can't be deployed to public read-only instance

[candleindark]

A read-only DB instance can't execute CREATE TEMPORARY TABLE which is a solution used in speeding up the calculation of stats in #353, so changes in #353 can be deployed publicly at the moment.

A good approach to solve this problem is configure the read-only database to allow the CREATE TEMPORARY TABLE.

The following is the log from the web instances of the read-only DL-Registry.

Traceback (most recent call last):
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 2190, in wsgi_app
    response = self.full_dispatch_request()
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 1486, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/app/datalad_registry/overview.py", line 73, in overview
    stats = get_collection_stats(base_select_stmt)
  File "/app/datalad_registry/blueprints/api/dataset_urls/tools.py", line 219, in get_collection_stats
    tmp_tb = cache_result_to_tmp_tb(select_stmt, "tmp_tb")
  File "/app/datalad_registry/blueprints/api/dataset_urls/tools.py", line 41, in cache_result_to_tmp_tb
    db.session.execute(text(create_tmp_tb_sql))
  File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/scoping.py", line 778, in execute
    return self._proxied.execute(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2200, in _execute_internal
    result = conn.execute(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1421, in execute
    return meth(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1643, in _execute_clauseelement
    ret = self._execute_context(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1849, in _execute_context
    return self._exec_single_context(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1989, in _exec_single_context
    self._handle_dbapi_exception(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1970, in _exec_single_context
    self.dialect.do_execute(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.ReadOnlySqlTransaction) cannot execute CREATE TABLE AS in a read-only transaction

[SQL:
        CREATE TEMPORARY TABLE tmp_tb AS
        SELECT repo_url.id, repo_url.url, repo_url.ds_id, repo_url.annex_uuid, repo_url.annex_key_count, repo_url.annexed_files_in_wt_count, repo_url.annexed_files_in_wt_size, repo_url.head, repo_url.head_describe, repo_url.head_dt, repo_url.branches, repo_url.tags, repo_url.git_objects_kb, repo_url.last_update_dt, repo_url.last_chk_dt, repo_url.chk_req_dt, repo_url.n_failed_chks, repo_url.processed, repo_url.cache_path
FROM repo_url;
    ]
(Background on this error at: https://sqlalche.me/e/20/2j85)
10.89.0.32 - - [25/Apr/2024 21:48:28] "GET /overview/?page=74&per_page=20&sort=annexed_files_in_wt_size-asc HTTP/1.1" 500 -