Contributors_aliases update of unique contraint

[cdolfi]

the restraint for alias email needs to be updated. the alias_email should be unique as a pair (alias_email, cntrb_id) but there can be an email associated with multiple cntb_id's

CREATE UNIQUE INDEX "contributor-alias-unique" ON augur_data.contributors_aliases USING btree (alias_email, cntrb_id); (most likely the old index needs to be dropped

[MoralCode]

Is this causing a particular stack trace/problem the way the index currently is? (i know the answer is probably yes, just asking so those details make it into this issue)

[cdolfi]

This is most likely causing errors on the insert_facade_contributors task (I came up with this issue before seeing that error).

Update: the cause of this error is #273

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.

[cdolfi]

Actually, I dont think this will fix that stack trace. That error is from the on conflict insert not bc of a current unique constraint. Somewhere else in the code its expecting a constraint on specifically the email that does not and should not exist

[MoralCode]

is this resolved by the same thing as #273? if so lets close it as a duplicate