UndefinedObject: collation "NOCASE" for encoding "UTF8" does not exist

[PhilWun]

This error appears when GETing /api/templates/ while using Postgres.

2023-06-13 11:34:24 2023-06-13 09:34:24,109 [ ERROR ] [app                           ] Exception on /api/templates/ [GET]    <log_exception, 1741; /usr/local/lib/python3.10/site-packages/flask/app.py>
2023-06-13 11:34:24 Traceback (most recent call last):
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
2023-06-13 11:34:24     self.dialect.do_execute(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
2023-06-13 11:34:24     cursor.execute(statement, parameters)
2023-06-13 11:34:24 psycopg2.errors.UndefinedObject: collation "NOCASE" for encoding "UTF8" does not exist
2023-06-13 11:34:24 LINE 2: FROM "UiTemplate" ORDER BY "UiTemplate".name COLLATE "NOCASE...
2023-06-13 11:34:24                                                      ^
2023-06-13 11:34:24 
2023-06-13 11:34:24 
2023-06-13 11:34:24 The above exception was the direct cause of the following exception:
2023-06-13 11:34:24 
2023-06-13 11:34:24 Traceback (most recent call last):
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app
2023-06-13 11:34:24     response = self.full_dispatch_request()
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request
2023-06-13 11:34:24     rv = self.handle_user_exception(e)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask_cors/extension.py", line 165, in wrapped_function
2023-06-13 11:34:24     return cors_after_request(app.make_response(f(*args, **kwargs)))
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request
2023-06-13 11:34:24     rv = self.dispatch_request()
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request
2023-06-13 11:34:24     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 107, in view
2023-06-13 11:34:24     return current_app.ensure_sync(self.dispatch_request)(**kwargs)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 188, in dispatch_request
2023-06-13 11:34:24     return current_app.ensure_sync(meth)(**kwargs)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/webargs/core.py", line 594, in wrapper
2023-06-13 11:34:24     return func(*args, **kwargs)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask_smorest/arguments.py", line 82, in wrapper
2023-06-13 11:34:24     return func(*f_args, **f_kwargs)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/flask_smorest/response.py", line 90, in wrapper
2023-06-13 11:34:24     func(*args, **kwargs)
2023-06-13 11:34:24   File "/app/qhana_plugin_registry/api/templates/root.py", line 84, in get
2023-06-13 11:34:24     templates: List[UiTemplate] = DB.session.execute(
2023-06-13 11:34:24   File "<string>", line 2, in execute
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
2023-06-13 11:34:24     result = conn._execute_20(statement, params or {}, execution_options)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
2023-06-13 11:34:24     return meth(self, args_10style, kwargs_10style, execution_options)
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
2023-06-13 11:34:24     return connection._execute_clauseelement(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
2023-06-13 11:34:24     ret = self._execute_context(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
2023-06-13 11:34:24     self._handle_dbapi_exception(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
2023-06-13 11:34:24     util.raise_(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
2023-06-13 11:34:24     raise exception
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
2023-06-13 11:34:24     self.dialect.do_execute(
2023-06-13 11:34:24   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
2023-06-13 11:34:24     cursor.execute(statement, parameters)
2023-06-13 11:34:24 sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) collation "NOCASE" for encoding "UTF8" does not exist
2023-06-13 11:34:24 LINE 2: FROM "UiTemplate" ORDER BY "UiTemplate".name COLLATE "NOCASE...
2023-06-13 11:34:24                                                      ^
2023-06-13 11:34:24 
2023-06-13 11:34:24 [SQL: SELECT "UiTemplate".id, "UiTemplate".name, "UiTemplate".description 
2023-06-13 11:34:24 FROM "UiTemplate" ORDER BY "UiTemplate".name COLLATE "NOCASE" ASC 
2023-06-13 11:34:24  LIMIT %(param_1)s]
2023-06-13 11:34:24 [parameters: {'param_1': 25}]
2023-06-13 11:34:24 (Background on this error at: https://sqlalche.me/e/14/f405)

[infacc]

The problem appears to be this line:

https://github.com/UST-QuAntiL/qhana-plugin-registry/blob/25c224e6dad5e5b146e8581b791952b081519975/qhana_plugin_registry/db/models/model_helpers.py#L65

Here the collate NOCASE is set. This is correct for a sqlite database, but for a postgresql database it should be POSIX (see here).

Unfortunately, we (currently) need both collates (NOCASE and POSIX) for running this project with docker (postresql) and without docker (sqlite).

[buehlefs]

If you know of a way to specify this collation info only for certain dialects of SQLAlchemy (maybe with a dynamic callback), then we could solve it that way.

Other potential other solutions are:

• Remove the collation information -> Sorting will be case sensitive again (not a huge deal)
• Remove the collation in the model but add it on a per query basis (this can be done based on the current engine and may not be too much work)
• Require postgres to have defined a NOCASE collation (postgres has support for defining custom collations [just alias POSIX], this would need a custom postgres container image/init script; may be possible to do in a migration script...)
• Create an SQLAchemy extension that does the dialect specific collation for us (we probably should not go this route)

We also need to account for the existing migration...