augur.tasks.github.events.collect_events: duplicate key value violates unique constraint "issue-insert-unique"

[cdolfi]

I think this error will get solved the same way as #3192

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "issue-insert-unique"\nDETAIL: Key (issue_url)=(https://api.github.com/repos/pulp/pulp-ui/issues/1) already exists.\n')

Stack Trace

Traceback (most recent call last):
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1938, in _exec_single_context
    self.dialect.do_executemany(
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 794, in do_executemany
    cursor.executemany(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "issue-insert-unique"
DETAIL:  Key (issue_url)=(https://api.github.com/repos/pulp/pulp-ui/issues/1) already exists.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 453, in trace_task
    R = retval = fun(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 736, in __protected_call__
    return self.run(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/augur/tasks/github/events.py", line 46, in collect_events
    collection_strategy.collect(repo_git, key_auth, core_data_last_collected)
  File "/augur/augur/tasks/github/events.py", line 128, in collect
    self._process_events(events, repo_id)
  File "/augur/augur/tasks/github/events.py", line 167, in _process_events
    update_issue_closed_cntrbs_by_repo_id(repo_id)
  File "/augur/augur/application/db/lib.py", line 555, in update_issue_closed_cntrbs_by_repo_id
    connection.execute(update_stmt, update_data)
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1938, in _exec_single_context
    self.dialect.do_executemany(
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 794, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "issue-insert-unique"
DETAIL:  Key (issue_url)=(https://api.github.com/repos/pulp/pulp-ui/issues/1) already exists.

[SQL: 
                UPDATE issues
                SET cntrb_id = %(cntrb_id)s
                WHERE issue_id = %(issue_id)s
                AND repo_id = %(repo_id)s
            ]
[parameters: [{'cntrb_id': UUID('01035995-d400-0000-0000-000000000000'), 'issue_id': 428805, 'repo_id': 27505}, {'cntrb_id': UUID('01035995-d400-0000-0000-000000000000'), 'issue_id': 428816, 'repo_id': 27505}, {'cntrb_id': UUID('0100047d-c300-0000-0000-000000000000'), 'issue_id': 428821, 'repo_id': 27505}, {'cntrb_id': UUID('01035995-d400-0000-0000-000000000000'), 'issue_id': 428823, 'repo_id': 27505}, {'cntrb_id': UUID('0101724c-7f00-0000-0000-000000000000'), 'issue_id': 212279091, 'repo_id': 27505}, {'cntrb_id': UUID('0100046b-cf00-0000-0000-000000000000'), 'issue_id': 212279092, 'repo_id': 27505}, {'cntrb_id': UUID('0100046b-cf00-0000-0000-000000000000'), 'issue_id': 212279102, 'repo_id': 27505}, {'cntrb_id': UUID('01008560-6200-0000-0000-000000000000'), 'issue_id': 212279103, 'repo_id': 27505}  ... displaying 10 of 23 total bound parameter sets ...  {'cntrb_id': UUID('0100046b-cf00-0000-0000-000000000000'), 'issue_id': 239085787, 'repo_id': 27505}, {'cntrb_id': UUID('010058be-8500-0000-0000-000000000000'), 'issue_id': 239085788, 'repo_id': 27505}]]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

https://github.com/pulp/pulp-ui'

[mn-ram]

Hi @cdolfi @MoralCode — opened PR #3801 to fix this.

The UPDATE in update_issue_closed_cntrbs_by_repo_id was using engine.connect() instead of engine.begin(), so the transaction was never committed in SQLAlchemy 2.0. One-line fix — matches the pattern used everywhere else in lib.py (lines 78, 88, 384, 426).

[MoralCode]

I responded in the PR

[MoralCode]

after reviewing the PR mentioned above, it is not a fix for this issue, even though the stack trace points directly to the query being run, its not actually that query that is causing the issue because that query never touches the issue_url, so it couldnt possibly cause this unique violation.

however, analysis by Gemini has led me to what may be the true cause of the issue

SELECT issue_url, COUNT(*) 
FROM augur_data.issues 
GROUP BY issue_url 
HAVING COUNT(*) > 1;

duplicate urls already exist in the table (somehow) despite the constraint existing. There are about 528 of them, meaning about 215 or so records with duplicate urls. I imagine these must have existed before the constraint was in place and somehow slipped under the radar.

The below query reveals the affected records in more detail

SELECT 
    issue_id, 
    issue_url, 
    repo_id, 
    cntrb_id, 
    created_at,
    tool_source,
    COUNT(*) OVER(PARTITION BY issue_url) as occurrence_count
FROM augur_data.issues
WHERE issue_url IN (
    SELECT issue_url 
    FROM augur_data.issues 
    GROUP BY issue_url 
    HAVING COUNT(*) > 1
)
ORDER BY issue_url, issue_id;