Solvers fail because of unique constraint violation

[mayaCostantini]

Describe the bug

Some solvers in thoth-middletier-stage fail because of a unique constraint violation for some packages:

2022-11-14 10:23:39,854   1 WARNING  thoth.storages.graph.models_base:56: Integrity error on creating a new record; this can be due to concurrent writes to database, recovering (attributes: {'package_name': 'ipywidgets', 'package_version': '6.0.0rc2', 'python_package_index_id': 1, 'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10', 'entity_id': 18050, 'package_license': None, 'package_license_warning': None}): (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "python_package_version_package_name_package_version_python__key"

Probably related to thoth-station/storages#2729

To Reproduce
Steps to reproduce the behavior:
See solver pod logs on stage cluster.

Expected behavior
Solver should skip packages that have already been solved and handle the exception.

Additional context
Full container log:

2022-11-14 10:23:18,458   1 INFO     thoth.common:315: Setting up logging to a Sentry instance 'sentry.io/1298083', environment 'ocp4-stage' and integrations ['SqlalchemyIntegration', 'AioHttpIntegration']
2022-11-14 10:23:18,956   1 INFO     thoth.graph_sync_job:251: graph-sync-job v0.10.23+thoth_storage.0.73.3+thoth_common.0.36.4 starting...
2022-11-14 10:23:18,958   1 DEBUG    thoth.graph_sync_job:224: Debug mode is on
2022-11-14 10:23:20,066   1 INFO     thoth.storages.graph.postgres:6005: Syncing solver result of package 'ipywidgets' in version '6.0.0rc2' license {'classifier': [['License :: OSI Approved :: BSD License', 'BSD License']], 'license': {'full_name': 'BSD 4-Clause "Original" or "Old" License', 'identifier': 'BSD 4 Clause', 'identifier_spdx': 'BSD-4-Clause'}, 'license_version': '4', 'warning': False} from 'https://pypi.org/simple' solved by {'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10'}
2022-11-14 10:23:24,592   1 INFO     thoth.storages.graph.postgres:6121: Syncing imported packages for package 'ipywidgets' in version '6.0.0rc2' from 'https://pypi.org/simple' found by solver {'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10'}
2022-11-14 10:23:39,791   1 INFO     thoth.storages.graph.postgres:6184: Syncing solver errors for package 'ipywidgets' in version '6.0.0.rc2' from 'https://pypi.org/simple' found by solver {'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10'}
2022-11-14 10:23:39,854   1 WARNING  thoth.storages.graph.models_base:56: Integrity error on creating a new record; this can be due to concurrent writes to database, recovering (attributes: {'package_name': 'ipywidgets', 'package_version': '6.0.0rc2', 'python_package_index_id': 1, 'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10', 'entity_id': 18050, 'package_license': None, 'package_license_warning': None}): (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "python_package_version_package_name_package_version_python__key"
DETAIL:  Key (package_name, package_version, python_package_index_id, os_name, os_version, python_version)=(ipywidgets, 6.0.0rc2, 1, fedora, 35, 3.10) already exists.

[SQL: INSERT INTO python_package_version (package_name, package_version, os_name, os_version, python_version, entity_id, python_package_index_id, python_package_metadata_id, is_missing, provides_source_distro, package_license, package_license_warning) VALUES (%(package_name)s, %(package_version)s, %(os_name)s, %(os_version)s, %(python_version)s, %(entity_id)s, %(python_package_index_id)s, %(python_package_metadata_id)s, %(is_missing)s, %(provides_source_distro)s, %(package_license)s, %(package_license_warning)s) RETURNING python_package_version.id]
[parameters: {'package_name': 'ipywidgets', 'package_version': '6.0.0rc2', 'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10', 'entity_id': 18050, 'python_package_index_id': 1, 'python_package_metadata_id': None, 'is_missing': False, 'provides_source_distro': True, 'package_license': None, 'package_license_warning': None}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2022-11-14 10:23:40,445   1 CRITICAL root:117: Traceback (most recent call last):
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "python_package_version_package_name_package_version_python__key"
DETAIL:  Key (package_name, package_version, python_package_index_id, os_name, os_version, python_version)=(ipywidgets, 6.0.0rc2, 1, fedora, 35, 3.10) already exists.


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

Traceback (most recent call last):
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/graph/models_base.py", line 52, in get_or_create
    session.commit()
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "python_package_version_package_name_package_version_python__key"
DETAIL:  Key (package_name, package_version, python_package_index_id, os_name, os_version, python_version)=(ipywidgets, 6.0.0rc2, 1, fedora, 35, 3.10) already exists.

[SQL: INSERT INTO python_package_version (package_name, package_version, os_name, os_version, python_version, entity_id, python_package_index_id, python_package_metadata_id, is_missing, provides_source_distro, package_license, package_license_warning) VALUES (%(package_name)s, %(package_version)s, %(os_name)s, %(os_version)s, %(python_version)s, %(entity_id)s, %(python_package_index_id)s, %(python_package_metadata_id)s, %(is_missing)s, %(provides_source_distro)s, %(package_license)s, %(package_license_warning)s) RETURNING python_package_version.id]
[parameters: {'package_name': 'ipywidgets', 'package_version': '6.0.0rc2', 'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10', 'entity_id': 18050, 'python_package_index_id': 1, 'python_package_metadata_id': None, 'is_missing': False, 'provides_source_distro': True, 'package_license': None, 'package_license_warning': None}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "app.py", line 252, in <module>
    cli()
  File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "app.py", line 226, in cli
    _do_sync(
  File "app.py", line 125, in _do_sync
    stats = sync_documents(
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/sync.py", line 616, in sync_documents
    stats_change = handler(
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/sync.py", line 143, in sync_solver_documents
    graph.sync_solver_result(document, force=force)
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/graph/postgres.py", line 6196, in sync_solver_result
    python_package_version_id = self._create_python_package_version(
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/graph/postgres.py", line 4771, in _create_python_package_version
    python_package_version, _ = PythonPackageVersion.get_or_create(
  File "/opt/app-root/lib64/python3.8/site-packages/thoth/storages/graph/models_base.py", line 62, in get_or_create
    return session.query(cls).filter_by(**kwargs).one(), True
  File "/opt/app-root/lib64/python3.8/site-packages/sqlalchemy/orm/query.py", line 3500, in one
    raise orm_exc.NoResultFound("No row was found for one()")
sqlalchemy.orm.exc.NoResultFound: No row was found for one()

Sentry is attempting to send 3 pending error messages
Waiting up to 2 seconds
Press Ctrl-C to quit

[mayaCostantini]

/sig stack-guidance
/priority critical-urgent

[harshad16]

Similar issue: thoth-station/storages#2308

[mayaCostantini]

Closing because duplicate of thoth-station/storages#2308

[harshad16]

Reopened the issue as it is little different than issue: thoth-station/storages#2308

• As the python_package_version constraints defined in issue

[parameters: {'package_name': 'google-resumable-media', 'package_version': '1.2.0', 'os_name': 'rhel', 'os_version': '8', 'python_version': '3.8', 'entity_id': 2438371, 'python_package_index_id': 1, 'python_package_metadata_id': 313979, 'is_missing': False, 'provides_source_distro': True}]

were different than the constraints defined in this issue.

[parameters: {'package_name': 'ipywidgets', 'package_version': '6.0.0rc2', 'os_name': 'fedora', 'os_version': '35', 'python_version': '3.10', 'entity_id': 18050, 'python_package_index_id': 1, 'python_package_metadata_id': None, 'is_missing': False, 'provides_source_distro': True, 'package_license': None, 'package_license_warning': None}]

from thoth-station/storages version v0.73.0, two additional fields were included in table PythonPackageVersion i.e https://github.com/thoth-station/storages/blob/f8d5406d45811ce38bd703f3726614cd571a7553/thoth/storages/graph/postgres.py#L4780
However, as the PythonPackageVersion has unique constraint https://github.com/thoth-station/storages/blob/f8d5406d45811ce38bd703f3726614cd571a7553/thoth/storages/graph/models.py#L92

So the inclusive causes psycopg2.errors.UniqueViolation. which in return throws sql sqlalchemy.exc.IntegrityError.

and when PostgreSQL tries to get the existing entry it wouldn't be able to capture it, as https://github.com/thoth-station/storages/blob/f8d5406d45811ce38bd703f3726614cd571a7553/thoth/storages/graph/models_base.py#L62 try to find an entry with all the field (include those two newly include fields) which doesn't exist.