swh-webapp-update-savecodenow-statuses.service times out because it uses a poorly indexed origin visit query
Latest log trace:
Apr 25 09:57:10 moma systemd[1]: Started Software Heritage Webapp Update SaveCodeNow Statuses.
Apr 25 09:57:12 moma django-admin[1767361]: Traceback (most recent call last):
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/bin/django-admin", line 5, in <module>
Apr 25 09:57:12 moma django-admin[1767361]: management.execute_from_command_line()
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
Apr 25 09:57:12 moma django-admin[1767361]: utility.execute()
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 375, in execute
Apr 25 09:57:12 moma django-admin[1767361]: self.fetch_command(subcommand).run_from_argv(self.argv)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 323, in run_from_argv
Apr 25 09:57:12 moma django-admin[1767361]: self.execute(*args, **cmd_options)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 364, in execute
Apr 25 09:57:12 moma django-admin[1767361]: output = self.handle(*args, **options)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/management/commands/refresh_savecodenow_statuses.py", line 27, in handle
Apr 25 09:57:12 moma django-admin[1767361]: refreshed_statuses = refresh_save_origin_request_statuses()
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/origin_save.py", line 650, in refresh_save_origin_request_statuses
Apr 25 09:57:12 moma django-admin[1767361]: if save_requests.count() > 0
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/origin_save.py", line 618, in update_save_origin_requests_from_queryset
Apr 25 09:57:12 moma django-admin[1767361]: task_runs.get(sor.loading_task_id),
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/origin_save.py", line 367, in _update_save_request_info
Apr 25 09:57:12 moma django-admin[1767361]: save_request
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/origin_save.py", line 305, in _check_visit_update_status
Apr 25 09:57:12 moma django-admin[1767361]: visit_date, visit_status = _get_visit_info_for_save_request(save_request)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/origin_save.py", line 285, in _get_visit_info_for_save_request
Apr 25 09:57:12 moma django-admin[1767361]: ovs = archive.origin_visit_find_by_date(origin, save_request.request_date)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/web/common/archive.py", line 1086, in origin_visit_find_by_date
Apr 25 09:57:12 moma django-admin[1767361]: visit = storage.origin_visit_find_by_date(origin_url, visit_date)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/core/api/__init__.py", line 182, in meth_
Apr 25 09:57:12 moma django-admin[1767361]: return self._post(meth._endpoint_path, post_data)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/core/api/__init__.py", line 279, in _post
Apr 25 09:57:12 moma django-admin[1767361]: return self._decode_response(response)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/core/api/__init__.py", line 371, in _decode_response
Apr 25 09:57:12 moma django-admin[1767361]: self.raise_for_status(response)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/storage/api/client.py", line 29, in raise_for_status
Apr 25 09:57:12 moma django-admin[1767361]: super().raise_for_status(response)
Apr 25 09:57:12 moma django-admin[1767361]: File "/usr/lib/python3/dist-packages/swh/core/api/__init__.py", line 361, in raise_for_status
Apr 25 09:57:12 moma django-admin[1767361]: raise exception from None
Apr 25 09:57:12 moma django-admin[1767361]: swh.core.api.RemoteException: <RemoteException 500 QueryCanceled: ['canceling statement due to statement timeout\nCONTEXT: PL/pgSQL function swh_visit_find_by_date(text,timestamp with time zone) line 6 at RETURN QUERY\n']>
Apr 25 09:57:12 moma systemd[1]: swh-webapp-update-savecodenow-statuses.service: Main process exited, code=exited, status=1/FAILURE
Apr 25 09:57:12 moma systemd[1]: swh-webapp-update-savecodenow-statuses.service: Failed with result 'exit-code'.This succeeds the second time, so the statuses eventually get updated.
Migrated from T4198 (view on Phabricator)