Skip to content

Add unique constraints instead of implicitly relying on uniqueness

Igor Ponomarev requested to merge add-unique-constraints into master

Closes #623

Every unique constraint is also an index. Avoid duplicated indexes. Replace any unique_together with a UniqueConstraint as recommended by Django documentation.

Some databases might already contain uniqueness violations which will prevent the migration from completing. One of the solutions is removing the offending entities.

For example, if there is TestJob that has multiple TestCases with "job" names. Following code can remove all TestJobs with duplicates:

q = TestJob.objects.annotate(job_cases=models.Count("pk", filter=models.Q(testsuite__testcase__name="job"))).filter(job_cases__gt=1)
q.delete()

Models changed:

lava_results_app
  TestSuite
    Add UniqueConstraint(job + name).
    Remove (job) index.
  TestSet
    Add UniqueConstraint(suite + name).
    Remove (suite) index.
  TestCase
    Add UniqueConstraint(suite) WHERE name = "job"
  Query
    Remove `unique_together`.
    Add UniqueConstraint(owner + name).
    Remove (owner) index.
  QueryOmitResult
    Remove `unique_together`.
    Add UniqueConstraint(query + content_type + object_id).
    Remove (query) index.
  ChartQueryUser
    Remove `unique_together`.
    Add UniqueConstraint(chart_query + user).
    Remove (chart_query) index.

lava_scheduler_app
  TestJob
    Switch current job prefetch index to UniqueConstraint.
    Add UniqueConstraint(sub_id) WHERE sub_id != ""
  NotificationRecipient
    Remove `unique_together`.
    Add UniqueConstraint(user + notification + method).
    Remove (user) index.
  TestJobUser
    Remove `unique_together`.
    Add UniqueConstraint(testjob + user).
    Remove (testjob) index.
  GroupDeviceTypePermission
    Remove `unique_together`.
    Add UniqueConstraint(devicetype + permission + group).
    Remove (devicetype) index.
  GroupDevicePermission
    Remove `unique_together`.
    Add UniqueConstraint(device + permission + group).
    Remove (device) index.
  GroupWorkerPermission
    Remove `unique_together`.
    Add UniqueConstraint(worker + permission + group).
    Remove (worker) index.
  RemoteArtifactsAuth
    Remove `unique_together`.
    Add UniqueConstraint(user + name).
    Remove (user) index.
Edited by Igor Ponomarev

Merge request reports