[Bug]: task query fails when using postgres

[aaronchongth]

Before proceeding, is there an existing issue or discussion for this?

• I have done a search for similar issues and discussions.

OS and version

Ubuntu 22.04

Open-RMF installation type

Source build

Other Open-RMF installation methods

No response

Open-RMF version or commit hash

humble

ROS distribution

Humble

ROS installation type

Binaries

Other ROS installation methods

No response

Package or library, if applicable

No response

Description of the bug

Using

• rmf-web on deploy/hammer
• postgres db

When going to the task tab, the task query fails

msg="127.0.0.1:43160 - \"GET /tasks?limit=10&offset=0&order_by=-unix_millis_start_time HTTP/1.1\" 500" level="INFO" ts="2024-06-19T22:12:36.182+0800" src="httptools_impl.py:474" funcName="send"
msg="Exception in ASGI application" level="ERROR" ts="2024-06-19T22:12:36.183+0800" src="httptools_impl.py:409" funcName="run_asgi" exc_info="Traceback (most recent call last):
  File "/home/aaron/.local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 36, in translate_exceptions_
    return await func(self, *args)
  File "/home/aaron/.local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 185, in execute_query
    rows = await connection.fetch(*params)
  File "/home/aaron/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 621, in fetch
    return await self._execute(
  File "/home/aaron/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 1659, in _execute
    result, _ = await self.__execute(
  File "/home/aaron/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
  File "/home/aaron/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 1711, in _do_execute
    stmt = await self._get_statement(
  File "/home/aaron/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 398, in _get_statement
    statement = await self._protocol.prepare(
  File "asyncpg/protocol/protocol.pyx", line 168, in prepare
asyncpg.exceptions.GroupingError: column "taskstate.data" must appear in the GROUP BY clause or be used in an aggregate function

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/aaron/.local/lib/python3.10/site-packages/uvicorn/protocols/http/httptools_impl.py", line 404, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/home/aaron/.local/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/fastapi/applications.py", line 269, in __call__
    await super().__call__(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/middleware/cors.py", line 92, in __call__
    await self.simple_response(scope, receive, send, request_headers=headers)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/middleware/cors.py", line 147, in simple_response
    await self.app(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/exceptions.py", line 93, in __call__
    raise exc
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/exceptions.py", line 82, in __call__
    await self.app(scope, receive, sender)
  File "/home/aaron/.local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/aaron/.local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/routing.py", line 670, in __call__
    await route.handle(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/routing.py", line 266, in handle
    await self.app(scope, receive, send)
  File "/home/aaron/.local/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/home/aaron/.local/lib/python3.10/site-packages/fastapi/routing.py", line 227, in app
    raw_response = await run_endpoint_function(
  File "/home/aaron/.local/lib/python3.10/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
    return await dependant.call(**values)
  File "/home/aaron/workspaces/rmf/demos-tasks/packages/api-server/api_server/routes/tasks/tasks.py", line 199, in query_task_states
    return await task_repo.query_task_states(query, pagination)
  File "/home/aaron/workspaces/rmf/demos-tasks/packages/api-server/api_server/repositories/tasks.py", line 155, in query_task_states
    results = await query.values_list("data", flat=True)
  File "/home/aaron/.local/lib/python3.10/site-packages/tortoise/queryset.py", line 1489, in _execute
    _, result = await self._db.execute_query(str(self.query))
  File "/home/aaron/.local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 38, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: column "taskstate.data" must appear in the GROUP BY clause or be used in an aggregate function"

It works well with sqlite, if we launch the api-server normally, pnpm start

Steps to reproduce the bug

1. Setup an empty postgres DB
2. Modify rmf-web/packages/api-server/psql_local_config.py to point to the DB
3. pnpm install to setup per normal
4. Start the dashboard, cd rmf-web/packages/dashboard, pnpm run start:react
5. Start the api-server using pnpm run start:psql
6. On the dashboard, click on the Task tab
7. The error will show up in the api-server terminal while the dashboard fetching doesn't work

Expected behavior

Task states to show up

Actual behavior

Continues to query, where each query fails

Additional information or screenshots

No response

[aaronchongth]

Commits before #949 is working

[koonpeng]

Just disable label sorting for now I guess. The bigger issue is that there is a chance a fix for this will not work in sqlite, then we need to either drop sqlite or maintain separate code paths. Or it could be that this is a tortoise/async-pg bug, need to try on main to see if that is the case.

[aaronchongth]

yeah unfortunately it is happening on main too, so this is perhaps some missing generations on postgres' side, leaving the logs here, https://gist.github.com/aaronchongth/004c3ecfb713aa1565774c20b450b446

Removing sorting via labels, will that affect filtering? If I would like to filter via __contains instead of exact matches?

[koonpeng]

#957