User repo listing unable to reliably produce complete results after pagination change

[cboylan]

• Gitea version (or commit ref): v1.12.0
• Git version: 2.20.1
• Operating system: Debian Buster
• Database (use [x]):
  • PostgreSQL
  • MySQL (really it is mariadb)
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
  • I haven't tried
• Log gist:
  NA

Description

#11827 Updated the user repo listing API to paginate. This pagination appears to rely on updated_unix to sort the repo list here https://github.com/go-gitea/gitea/blob/master/models/repo.go#L1780. There are two problems with this that arise from unstable sorting across subsequent requests to the api for different pages.

First, if two repos have the same updated_unix value and they straddle page boundaries then due to the database not returning equal values with a stable sort using ORDER BY we can lose one of the two repos because the other will show up at the end of page n and the beginning of page n + 1.

Second, if repos are being modified while they are being listed their updated_unix time will change. This means that a repo listing could lose repos because they were initially at the end of the list but then have their updated time updated and are now at the beginning of the list but the user has moved on to listing later pages.

To fix this we should likely be doing ORDER BY using id and not updated_unix.

Note the first issue has been observed with our testing deployments. I've not confirmed the the second happens in practice as in theory db sessions could be held open across web sessions or similar though reading the code I don't think this is happening.

Also, much of the API listing methods now paginate. I expect there may be similar issues there but have not confirmed this.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

[stale]

This issue has been automatically closed because of inactivity. You can re-open it if needed.