RepoSync failure workaround

I found my self hosted instance would no longer sync repo's reliably. Digging thru the logs what I found is that every time it would run I would hit an error like this:

{
    "message": "Upserting repo",
    "asctime": "2025-05-01 20:09:31,064",
    "name": "tasks.sync_repos",
    "levelname": "INFO",
    "lineno": 512,
    "pathname": "/app/apps/worker/tasks/sync_repos.py",
    "funcName": "upsert_repo",
    "threadName": "ThreadPoolExecutor-423_0",
    "ownerid": 3,
    "repo_data": {
        "service_id": "898684111",
        "name": "edml_smtcmp_autopgrui",
        "language": "typescript",
        "private": true,
        "branch": "main"
    },
    "context": {
        "task_name": "app.tasks.sync_repos.SyncRepos",
        "task_id": "b475acb7-bb6d-4b2a-9119-fdb223e57332",
        "owner_username": "JonesT41_cvsh",
        "owner_service": "github",
        "owner_plan": "users-pr-inappy",
        "owner_id": 4,
        "repo_name": null,
        "repo_id": null,
        "commit_sha": null,
        "commit_id": null,
        "sentry_trace_id": null
    },
    "logger.name": "tasks.sync_repos",
    "logger.thread_name": "ThreadPoolExecutor-423_0",
    "level": "INFO"
}

{
    "message": "Database seems to be unavailable",
    "asctime": "2025-05-01 20:09:31,074",
    "name": "worker",
    "levelname": "WARNING",
    "lineno": 210,
    "pathname": "/app/apps/worker/tasks/base.py",
    "funcName": "_analyse_error",
    "threadName": "MainThread",
    "exc_info": "Traceback (most recent call last):\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1276, in _execute_context\n    self.dialect.do_execute(\n    ~~~~~~~~~~~~~~~~~~~~~~~^\n        cursor, statement, parameters, context\n        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py\", line 608, in do_execute\n    cursor.execute(statement, parameters)\n    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^\npsycopg2.errors.TriggeredDataChangeViolation: tuple to be updated was already modified by an operation triggered by the current command\nHINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.\n\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n  File \"/app/apps/worker/tasks/base.py\", line 261, in run\n    return self.run_impl(db_session, *args, **kwargs)\n           ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/app/apps/worker/tasks/sync_repos.py\", line 116, in run_impl\n    sync_repos_output = async_to_sync(self.sync_repos)(\n        db_session, git, owner, username, using_integration\n    )\n  File \"/usr/local/lib/python3.13/site-packages/asgiref/sync.py\", line 254, in __call__\n    return call_result.result()\n           ~~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/concurrent/futures/_base.py\", line 449, in result\n    return self.__get_result()\n           ~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/concurrent/futures/_base.py\", line 401, in __get_result\n    raise self._exception\n  File \"/usr/local/lib/python3.13/site-packages/asgiref/sync.py\", line 331, in main_wrap\n    result = await self.awaitable(*args, **kwargs)\n             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/app/apps/worker/tasks/sync_repos.py\", line 417, in sync_repos\n    process_repos(page)\n    ~~~~~~~~~~~~~^^^^^^\n  File \"/app/apps/worker/tasks/sync_repos.py\", line 413, in process_repos\n    db_session.commit()\n    ~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 1046, in commit\n    self.transaction.commit()\n    ~~~~~~~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 504, in commit\n    self._prepare_impl()\n    ~~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 483, in _prepare_impl\n    self.session.flush()\n    ~~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 2540, in flush\n    self._flush(objects)\n    ~~~~~~~~~~~^^^^^^^^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 2681, in _flush\n    with util.safe_reraise():\n         ~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/util/langhelpers.py\", line 68, in __exit__\n    compat.raise_(\n    ~~~~~~~~~~~~~^\n        exc_value,\n        ^^^^^^^^^^\n        with_traceback=exc_tb,\n        ^^^^^^^^^^^^^^^^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/util/compat.py\", line 182, in raise_\n    raise exception\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py\", line 2642, in _flush\n    flush_context.execute()\n    ~~~~~~~~~~~~~~~~~~~~~^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/unitofwork.py\", line 422, in execute\n    rec.execute(self)\n    ~~~~~~~~~~~^^^^^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/unitofwork.py\", line 586, in execute\n    persistence.save_obj(\n    ~~~~~~~~~~~~~~~~~~~~^\n        self.mapper,\n        ^^^^^^^^^^^^\n        uow.states_for_mapper_hierarchy(self.mapper, False, False),\n        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n        uow,\n        ^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/persistence.py\", line 230, in save_obj\n    _emit_update_statements(\n    ~~~~~~~~~~~~~~~~~~~~~~~^\n        base_mapper,\n        ^^^^^^^^^^^^\n    ...<4 lines>...\n        update,\n        ^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/persistence.py\", line 994, in _emit_update_statements\n    c = cached_connections[connection].execute(\n        statement, multiparams\n    )\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1011, in execute\n    return meth(self, multiparams, params)\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/sql/elements.py\", line 298, in _execute_on_connection\n    return connection._execute_clauseelement(self, multiparams, params)\n           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1124, in _execute_clauseelement\n    ret = self._execute_context(\n        dialect,\n    ...<4 lines>...\n        distilled_params,\n    )\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1316, in _execute_context\n    self._handle_dbapi_exception(\n    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^\n        e, statement, parameters, cursor, context\n        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1510, in _handle_dbapi_exception\n    util.raise_(\n    ~~~~~~~~~~~^\n        sqlalchemy_exception, with_traceback=exc_info[2], from_=e\n        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/util/compat.py\", line 182, in raise_\n    raise exception\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py\", line 1276, in _execute_context\n    self.dialect.do_execute(\n    ~~~~~~~~~~~~~~~~~~~~~~~^\n        cursor, statement, parameters, context\n        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n    )\n    ^\n  File \"/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py\", line 608, in do_execute\n    cursor.execute(statement, parameters)\n    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^\nsqlalchemy.exc.OperationalError: (psycopg2.errors.TriggeredDataChangeViolation) tuple to be updated was already modified by an operation triggered by the current command\nHINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.\n\n[SQL: UPDATE repos SET name=%(name)s, updatestamp=%(updatestamp)s WHERE repos.repoid = %(repos_repoid)s]\n[parameters: {'name': 'edml_smtcmp_autopgrui', 'updatestamp': datetime.datetime(2025, 5, 1, 20, 9, 31, 68255), 'repos_repoid': 17586}]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)",
    "task_args": [
        [
            null
        ],
        {
            "ownerid": 4,
            "username": "JonesT41_cvsh",
            "using_integration": false,
            "manual_trigger": true,
            "repository_service_ids": null
        }
    ],
    "task_kwargs": {},
    "context": {
        "task_name": "app.tasks.sync_repos.SyncRepos",
        "task_id": "b475acb7-bb6d-4b2a-9119-fdb223e57332",
        "owner_username": "JonesT41_cvsh",
        "owner_service": "github",
        "owner_plan": "users-pr-inappy",
        "owner_id": 4,
        "repo_name": null,
        "repo_id": null,
        "commit_sha": null,
        "commit_id": null,
        "sentry_trace_id": null
    },
    "logger.name": "worker",
    "logger.thread_name": "MainThread",
    "level": "WARNING"
}

From there I went and searched the repos table and saw that there was already an entry for edml_smtcmp_autopgrUI where the name had different casing. If I attempted to edit just the name field directly I would get the same error. However I was able to delete the full row without issue.

After deleting the entry and restarting the sync (or waiting for the auto retry task) it was able to get past that repo when running.

I have no idea if there are any negative side effects from deleting the row manually like this yet (or if there is a better fix I just don't know of). But for now it has unblocked my instance from adding repo's.

Start with apps/worker/tasks/sync_repos.py around upsert_repo and sync_repos, then inspect the PostgreSQL trigger affecting the repos table; the error suggests a BEFORE trigger is involved. Reproduce the case-variant repository sync and verify that it completes without manually deleting the row, while preserving existing repository data.