v0.73.6 has NotNullViolation change in its alembic version

[harshad16]

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

storages/thoth/storages/graph/models.py

Line 1291 in f8d5406

software_environment_id = Column(

software environment id is set primary key,causing the not null issue.

[goern]

/priority critical-urgent
/assign @mayaCostantini

[mayaCostantini]

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

[codificat]

/sig stack-guidance

[harshad16]

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]

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.