Bug: incorrect natural key declaration for alias_insert

[cdolfi]

bulk_insert_dicts(logger, alias_clean, ContributorsAlias, ['alias_email'])

This defines the email as a unique key, it should be based on (alias_email, cntrb_id) not just the email as multiple cntrb_id could have the same email

Error caused by this:

Exception | ValueError('task_id must not be empty. Got None instead.')

Stack Trace

Traceback (most recent call last):
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification


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/facade_github/tasks.py", line 253, in insert_facade_contributors
    process_commit_metadata(logger, key_auth, batch, repo_id, platform_id, tool_source, tool_version, data_source)
  File "/augur/augur/tasks/github/facade_github/tasks.py", line 126, in process_commit_metadata
    insert_alias(logger, cntrb, emailFromCommitData)
  File "/augur/augur/tasks/github/facade_github/contributor_interfaceable/contributor_interface.py", line 189, in insert_alias
    bulk_insert_dicts(logger, alias_clean, ContributorsAlias, ['alias_email'])
  File "/augur/augur/application/db/lib.py", line 403, in bulk_insert_dicts
    raise e
  File "/augur/augur/application/db/lib.py", line 385, in bulk_insert_dicts
    connection.execute(stmnt)
  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 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnReference) there is no unique or exclusion constraint matching the ON CONFLICT specification

[SQL: INSERT INTO augur_data.contributors_aliases (cntrb_alias_id, cntrb_id, canonical_email, alias_email) VALUES (nextval('augur_data.contributors_aliases_cntrb_alias_id_seq'), %(cntrb_id_m0)s::UUID, %(canonical_email_m0)s, %(alias_email_m0)s) ON CONFLICT (alias_email) DO UPDATE SET cntrb_id = coalesce(excluded.cntrb_id, augur_data.contributors_aliases.cntrb_id), canonical_email = coalesce(excluded.canonical_email, augur_data.contributors_aliases.canonical_email), alias_email = coalesce(excluded.alias_email, augur_data.contributors_aliases.alias_email)]
[parameters: {'cntrb_id_m0': UUID('010001e5-6b00-0000-0000-000000000000'), 'canonical_email_m0': '124267+tomsobpl@users.noreply.github.com', 'alias_email_m0': '124267+tomsobpl@users.noreply.github.com'}]
(Background on this error at: https://sqlalche.me/e/20/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/redis.py", line 533, in on_chord_part_return
    resl = [unpack(tup, decode) for tup in resl]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/redis.py", line 533, in <listcomp>
    resl = [unpack(tup, decode) for tup in resl]
            ^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/redis.py", line 439, in _unpack_chord_result
    raise ChordError(f'Dependency {tid} raised {retval!r}')
celery.exceptions.ChordError: Dependency e6e8d4b3-3919-4505-ab30-d8f07fca9c04 raised Exception("<class 'sqlalchemy.exc.ProgrammingError'>(['(psycopg2.errors.InvalidColumnReference) there is no unique or exclusion constraint matching the ON CONFLICT specification\\n'])")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 470, in trace_task
    I, R, state, retval = on_error(task_request, exc)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 381, in on_error
    R = I.handle_error_state(
        ^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 175, in handle_error_state
    return {
           ^
  File "/augur/.venv/lib/python3.11/site-packages/celery/app/trace.py", line 227, in handle_failure
    task.backend.mark_as_failure(
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 172, in mark_as_failure
    self.on_chord_part_return(request, state, exc)
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/redis.py", line 552, in on_chord_part_return
    return self.chord_error_from_stack(callback, exc)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 304, in chord_error_from_stack
    return backend.fail_from_current_stack(callback.id, exc=exc)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 311, in fail_from_current_stack
    self.mark_as_failure(task_id, exc, exception_info.traceback)
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 167, in mark_as_failure
    self.store_result(task_id, exc, state,
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 526, in store_result
    self._store_result(task_id, result, state, traceback,
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 975, in _store_result
    current_meta = self._get_task_meta_for(task_id)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 997, in _get_task_meta_for
    meta = self.get(self.get_key_for_task(task_id))
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/augur/.venv/lib/python3.11/site-packages/celery/backends/base.py", line 871, in get_key_for_task
    raise ValueError(f'task_id must not be empty. Got {task_id} instead.')
ValueError: task_id must not be empty. Got None instead.

[MoralCode]

the table model defines the unique key as the same single email field.

CollectOSS/collectoss/application/db/models/augur_data.py

Line 783 in c986c8a

UniqueConstraint("alias_email"),

They should probably both change together

[cdolfi]

@MoralCode good catch, so both need to get changed to:

bulk_insert_dicts(logger, alias_clean, ContributorsAlias, ['cntrb_id','alias_email'])

UniqueConstraint("alias_email","cntrb_id", name="cntrb-email-insert-unique"),

And that should fix the problem?

[MoralCode]

its hard to answer "should this fix the problem" without knowing how the problem happens.

Based on the stack trace, this would make sense, but im always wary that the stack trace we see may only be a secondary effect of something else.

What repos is this failing on? is this failing every single time collection tries to add a new alias for someone?

[cdolfi]

Its failing in all cases. There is a mismatch between the constraints its expecting and what is there. I took out the unique constraint on just the "alias_email" in my manual process of adding in the aliases that were missing bc it would not allow me to add in the rows with the same "alias_email" that were valid. That probably exposed the issue of the incorrect constraint by there being a mismatch issue. I do think this will fix the problem, we will just need to make sure (i think it will) that augur will add in the aliases that werent able to be added during this time

[MoralCode]

we will just need to make sure (i think it will) that collectoss will add in the aliases that werent able to be added during this time

the task runs process_commit_metadata before link_commits_to_contributor, and there is no exception handling to catch the exception until it hits the celery task. so this should mean that contributors that fail to insert an alias will fail the entire job. meaning that contributors up to the failure would be inserted into the contributors table, the one affected contributor may be missing an alias, and the remaining contributors in the current task are skipped, with the linking step never running at all, so theoretically all of them should be retried next run.

[MoralCode]

The only problem i see is potentially an instance-specific one where alembic may freak out when it tries to remove a constraint that it thinks should exist but doesnt.