Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
candleindark opened this issue Apr 25, 2024 · 0 comments · May be fixed by #370
Open

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

candleindark opened this issue Apr 25, 2024 · 0 comments · May be fixed by #370
Labels
bug Something isn't working

Comments

@candleindark
Copy link
Collaborator

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 -
@candleindark candleindark added the bug Something isn't working label Apr 25, 2024
@candleindark candleindark changed the title Temporary table can't be created in read-only DB. Changes in #353 can't be deploy to public read-only instance Temporary table can't be created in read-only DB. Changes in #353 can't be deployed to public read-only instance Apr 26, 2024
This was referenced May 8, 2024
Open
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Replace temporary table with CTE in stats calculation candleindark/datalad-registry
1 participant
@candleindark