[Bug]: task query fails when using postgres
Closed this issue · 4 comments
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
ondeploy/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
- Setup an empty postgres DB
- Modify
rmf-web/packages/api-server/psql_local_config.py
to point to the DB pnpm install
to setup per normal- Start the dashboard,
cd rmf-web/packages/dashboard
,pnpm run start:react
- Start the api-server using
pnpm run start:psql
- On the dashboard, click on the Task tab
- 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
Commits before #949 is working
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.
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?