Skip to content

Background migration: update projects updated_at by greatest timestamp

Jonas Wälter requested to merge siemens/gitlab:projects/order-by-updated_at into master

What does this MR do and why?

A project list is displayed in various places in GitLab (e.g. dashboard or Explore projects page). For each project in the list, it shows when it was last updated (e.g. "Updated 1 week ago"). This text is based on the maximum of three timestamps of a project: last_activity_at, last_repository_updated_at, and updated_at. This is fine and makes sense.

However, there is a problem with sorting: You can sort the list by "Last updated" (or "Oldest updated"), but unfortunately this sorting option only considers the last_activity_at timestamp. This causes that the project list doesn't seem sorted correctly for the user (see screenshots below).

  1. In !78572 (merged), the first step to solve this problem was done: The updated_at property now contains the actual timestamp of the last update (considering last_activity_at and last_repository_updated_at as well).
  2. The second step is done in this MR: If you sort the list by "Last updated" (or "Oldest updated"), it will consider updated_at instead of last_activity_at. A background migration updates projects.updated_at by the greatest timestamp of last_activity_at, last_repository_updated_at and updated_at.
  3. In a follow-up MR: If you sort the list by "Last updated" (or "Oldest updated"), it will consider updated_at instead of last_activity_at.

Preceding approaches:

  • !75350 (closed): First attempt to solve the problem by adding an index using GREATEST(updated_at, last_activity_at, last_repository_updated_at) (closed due to datetime inconsistencies)
  • !77497 (closed): Second attempt to solve the problem by adding a separate column containing the actual greatest timestamp using a trigger (closed in favor of this MR as the solution is not ideal from a database point of view)

🛠 with ❤ at Siemens

/cc @bufferoverflow

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Jonas Wälter

Merge request reports