search

UST-QuAntiL / qhana-plugin-registry

A plugin registry for QHAna plugins
https://qhana-plugin-registry.readthedocs.io/en/latest
Apache License 2.0
0 stars 0 forks source link

Postgres: DB error when getting recommendations #22

Closed infacc closed 1 year ago

infacc commented 1 year ago

The plugin registry throws a ProgrammingError when getting recommendations. E.g. when selecting a timeline step in the UI.

This bug occurs when running the qhana-docker compose setup. Locally the recommendations work as intended. I suppose it is related to using a PostgreSQL database.

Error log:

2023-08-23 08:17:46 2023-08-23 06:17:46,266 [ ERROR ] [app                           ] Exception on /api/recommendations/ [GET]    <log_exception, 1741; /usr/local/lib/python3.10/site-packages/flask/app.py>
2023-08-23 08:17:46 Traceback (most recent call last):
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app
2023-08-23 08:17:46     response = self.full_dispatch_request()
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request
2023-08-23 08:17:46     rv = self.handle_user_exception(e)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask_cors/extension.py", line 165, in wrapped_function
2023-08-23 08:17:46     return cors_after_request(app.make_response(f(*args, **kwargs)))
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request
2023-08-23 08:17:46     rv = self.dispatch_request()
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request
2023-08-23 08:17:46     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 107, in view
2023-08-23 08:17:46     return current_app.ensure_sync(self.dispatch_request)(**kwargs)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 188, in dispatch_request
2023-08-23 08:17:46     return current_app.ensure_sync(meth)(**kwargs)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/webargs/core.py", line 594, in wrapper
2023-08-23 08:17:46     return func(*args, **kwargs)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask_smorest/arguments.py", line 82, in wrapper
2023-08-23 08:17:46     return func(*f_args, **f_kwargs)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/flask_smorest/response.py", line 90, in wrapper
2023-08-23 08:17:46     func(*args, **kwargs)
2023-08-23 08:17:46   File "/app/qhana_plugin_registry/api/recommendations/root.py", line 95, in get
2023-08-23 08:17:46     votes = get_recommendations(full_context, timeout)[:limit]
2023-08-23 08:17:46   File "/app/qhana_plugin_registry/recommendations/voting.py", line 174, in get_recommendations
2023-08-23 08:17:46     group_result.get(timeout=timeout)
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/celery/result.py", line 677, in get
2023-08-23 08:17:46     return (self.join_native if self.supports_native_join else self.join)(
2023-08-23 08:17:46   File "/usr/local/lib/python3.10/site-packages/celery/result.py", line 808, in join_native
2023-08-23 08:17:46     raise value
2023-08-23 08:17:46 Exception: <class 'sqlalchemy.exc.ProgrammingError'>(['(psycopg2.errors.GroupingError) column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c...\n                                                          ^\n'])
buehlefs commented 1 year ago

The actual error happens in a celery task. However, the API should not fail because of an error in a recommender task. That part is easy to fix. Can you also provide the stacktrace from the original exception (it should be in the worker log)? The last line unfortunately does not contain the full stacktrace.

infacc commented 1 year ago

Here is the corresponding worker log:

[2023-08-23 10:43:34,623: ERROR/MainProcess] Task qhana_plugin_registry.recommendations.recommenders.step_data_recommender.fetch_votes[76a42cdf-ab85-4600-8454-913f018eb486] raised unexpected: ProgrammingError('(psycopg2.errors.GroupingError) column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c...\n                                                          ^\n')
Traceback (most recent call last):
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.GroupingError: column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c...
                                                          ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/celery/app/trace.py", line 451, in trace_task
    R = retval = fun(*args, **kwargs)
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/qhana_plugin_registry/celery.py", line 41, in __call__
    return self.run(*args, **kwargs)
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/qhana_plugin_registry/recommendations/recommenders/step_data_recommender.py", line 112, in fetch_votes
    ids: Sequence[Tuple[int, int, int]] = DB.session.execute(q).all()
  File "<string>", line 2, in execute
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c...
                                                          ^

[SQL: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.count_2 
FROM "DataToRamp" LEFT OUTER JOIN (SELECT "DataToRamp".ramp_id AS ramp_id, count(*) AS count_2 
FROM "DataToRamp" 
WHERE "DataToRamp".relation = %(relation_1)s AND "DataToRamp".required = true GROUP BY "DataToRamp".ramp_id) AS required ON "DataToRamp".ramp_id = required.ramp_id 
WHERE "DataToRamp".relation = %(relation_2)s AND "DataToRamp".required = true AND ("DataToRamp".data_type_start = %(data_type_start_1)s OR "DataToRamp".data_type_start = %(data_type_start_2)s) AND ("DataToRamp".data_type_end = %(data_type_end_1)s OR "DataToRamp".data_type_end = %(data_type_end_2)s) AND (EXISTS (SELECT "ContentTypeToData".data_id 
FROM "ContentTypeToData" 
WHERE "DataToRamp".id = "ContentTypeToData".data_id AND ("ContentTypeToData".content_type_start = %(content_type_start_1)s OR "ContentTypeToData".content_type_start = %(content_type_start_2)s) AND ("ContentTypeToData".content_type_end = %(content_type_end_1)s OR "ContentTypeToData".content_type_end = %(content_type_end_2)s))) GROUP BY "DataToRamp".ramp_id]
[parameters: {'relation_1': 'consumed', 'relation_2': 'consumed', 'data_type_start_1': 'executable', 'data_type_start_2': '*', 'data_type_end_1': 'circuit', 'data_type_end_2': '*', 'content_type_start_1': 'text', 'content_type_start_2': '*', 'content_type_end_1': 'x-qasm', 'content_type_end_2': '*'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
buehlefs commented 1 year ago

Can you test if the potential fix in #23 works with postgres?

infacc commented 1 year ago

The fix works for the timeline recommendations. I added it also to the data recommendations (same problem).

Error log ``` [2023-08-23 11:46:32,545: ERROR/MainProcess] Task qhana_plugin_registry.recommendations.recommenders.current_data_recommender.fetch_votes[01d3f2e9-1312-461a-bce4-21f90f1d85cd] raised unexpected: ProgrammingError('(psycopg2.errors.GroupingError) column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c...\n ^\n') Traceback (most recent call last): File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.GroupingError: column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/celery/app/trace.py", line 451, in trace_task R = retval = fun(*args, **kwargs) File "/mnt/e/Temp/hiwi/qhana-plugin-registry/qhana_plugin_registry/celery.py", line 41, in __call__ return self.run(*args, **kwargs) File "/mnt/e/Temp/hiwi/qhana-plugin-registry/qhana_plugin_registry/recommendations/recommenders/current_data_recommender.py", line 97, in fetch_votes ids: Sequence[Tuple[int, int, int]] = DB.session.execute(q).all() File "", line 2, in execute File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection return connection._execute_clauseelement( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception util.raise_( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/mnt/e/Temp/hiwi/qhana-plugin-registry/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "required.count_2" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.c... ^ [SQL: SELECT "DataToRamp".ramp_id, count(*) AS count_1, required.count_2 FROM "DataToRamp" LEFT OUTER JOIN (SELECT "DataToRamp".ramp_id AS ramp_id, count(*) AS count_2 FROM "DataToRamp" WHERE "DataToRamp".relation = %(relation_1)s AND "DataToRamp".required = true GROUP BY "DataToRamp".ramp_id) AS required ON "DataToRamp".ramp_id = required.ramp_id WHERE "DataToRamp".relation = %(relation_2)s AND "DataToRamp".required = true AND ("DataToRamp".data_type_start = %(data_type_start_1)s OR "DataToRamp".data_type_start = %(data_type_start_2)s) AND ("DataToRamp".data_type_end = %(data_type_end_1)s OR "DataToRamp".data_type_end = %(data_type_end_2)s) AND (EXISTS (SELECT "ContentTypeToData".data_id FROM "ContentTypeToData" WHERE "DataToRamp".id = "ContentTypeToData".data_id AND ("ContentTypeToData".content_type_start = %(content_type_start_1)s OR "ContentTypeToData".content_type_start = %(content_type_start_2)s) AND ("ContentTypeToData".content_type_end = %(content_type_end_1)s OR "ContentTypeToData".content_type_end = %(content_type_end_2)s))) GROUP BY "DataToRamp".ramp_id] [parameters: {'relation_1': 'consumed', 'relation_2': 'consumed', 'data_type_start_1': 'provenance', 'data_type_start_2': '*', 'data_type_end_1': 'execution-options', 'data_type_end_2': '*', 'content_type_start_1': 'application', 'content_type_start_2': '*', 'content_type_end_1': 'json', 'content_type_end_2': '*'}] (Background on this error at: https://sqlalche.me/e/14/f405) ```