search

thoth-station / storages

Storage and database adapters for project Thoth
https://thoth-station.github.io/
GNU General Public License v3.0
14 stars 16 forks source link

v0.73.6 has NotNullViolation change in its alembic version #2745

Closed harshad16 closed 1 year ago

harshad16 commented 1 year ago

Bug description

The data sync with the latest storage version v0.73.6 is causing NotNullViolation.

  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.NotNullViolation) column "software_environment_id" contains null values

case: op.alter_column("has_symbol", "software_environment_id", existing_type=sa.INTEGER(), nullable=False) cant be used as psycopg2.errors.NotNullViolation: column "software_environment_id" contains null values

Steps to Reproduce

Steps to reproduce the behavior:

  1. Try schema update with latest alembic version on stage/test cluster dump
  2. See error

Actual behavior

data schema update is failing with NotNull Violation

Expected behavior

smooth data schema update

Environment information

Storage version v0.73.6 revision head: 'bf9ea2a38b01' database head: '7f7411e247d6'

Additional context

Based on condition https://github.com/thoth-station/storages/blob/master/thoth/storages/graph/postgres.py#L5554, if an external software environment id is present then software environment id is set null. However https://github.com/thoth-station/storages/blob/f8d5406d45811ce38bd703f3726614cd571a7553/thoth/storages/graph/models.py#L1291 software environment id is set primary key,causing the not null issue.

goern commented 1 year ago

/priority critical-urgent /assign @mayaCostantini

mayaCostantini commented 1 year ago

I will look into it, thanks for opening the issue. The implementation I proposed in https://github.com/thoth-station/storages/pull/2738 should have caused it. What we would need is ideally support for NULL NOT DISTINCT in UniqueConstraint as already proposed in https://github.com/sqlalchemy/sqlalchemy/issues/8240 upstream I'll try to find an alternative solution in the meantime.

codificat commented 1 year ago

/sig stack-guidance

harshad16 commented 1 year ago

Closing following the tables, it can be noticed that the has_symbol table has details about the software_environment and external_software_environment relationship with version_symbols. Given that, at once either software_environment or external_software_environment can have a value corresponding to version_symbols in the has_symbol table.

The software_environment_id field in the has_symbol table shouldn't be a primary key.

harshad16 commented 1 year ago

With thoth-storages release v0.74.0, the database is able upgrade to revision head: 'bf9ea2a38b01'. closing this issue as it is resolved on the cluster.