[Question] Can PythonPackageVersionEntity have multiple same value

[harshad16]

The database shows multiple entry of the same unique tuple

even though we have set the unique constriat

storages/thoth/storages/graph/models.py

Line 203 in a69733c

UniqueConstraint("package_name", "package_version", "python_package_index_id"),

The python_package_index_id, seems to be the differentiating factor, which somehow hasn't been included.

[harshad16]

/sig stack-guidance

[mayaCostantini]

Which is the postgres image used to run the database? Is the version < 15?
Postgres versions before 15 seems to consider NULL values as distinct, which would impose to use something like postgresql_where=python_package_index_id.isnot(None) in the python_package_version_entity_idx index:

storages/thoth/storages/graph/models.py

Lines 204 to 210 in a69733c

	Index(
	"python_package_version_entity_idx",
	"package_name",
	"package_version",
	"python_package_index_id",
	unique=True,
	),

      Index(
          "python_package_version_entity_idx",
          "package_name",
          "package_version",
          "python_package_index_id",
          postgresql_where=python_package_index_id.isnot(None),
          unique=True,
          ),

I have not tested this solution though as I still have issues with podman. I will sort this out and get back to you if that works.

[harshad16]

we use Postgresql 10 in our systems.
I wasn't aware that the older version considered NULL as distinct.

Maybe it is time to upgrade to a newer version of PostgreSQL and clean the database a little.
we should identify what are the change that we would have to make for such upgrade

[mayaCostantini]

Agree, I can assign myself to this issue and start working on it when I solve my testing environment issues. What would you say is the priority of this?

[mayaCostantini]

Also, this might be related to #2189

[harshad16]

#2189 could be related
It would be a priority as I feel this might affect the data ingestion.

[harshad16]

Questions:

• Isn't there a get_or_create provided by sqlalchemy itself ? Catching integrity error + session.rollback looks like the job of a context manager