[v13.0/forgejo] fix: db.Iterate can miss records, can return records twice #9723

Merged

Gusted merged 1 commit from bp-v13.0/forgejo-79f6f8e into v13.0/forgejo

2025-10-17 06:45:30 +02:00

forgejo-backport-action commented

2025-10-17 00:17:08 +02:00

Member

Backport: #9657

Fixes #9644.

Rewrites db.Iterate so that it performs DB queries in this format:

First: SELECT ...columns... FROM table ORDER BY id LIMIT ...buffer-size...
Subsequent buffer fills: adding a WHERE id > ...last-id-from-previous...

This approach:

Prevents records from being missed or returned twice
Returns records in a predictable order
Should be faster, by virtue of using database indexes on the primary key to perform the query
Doesn't rely on any unpredictable database behaviour when using LIMIT and OFFSET without an ORDER BY
(Downside: does require reflection to read field values off Go structures for the primary key value)

Expands the automated tests to include the predicted failure case identified in #9644, which verified the previous broken behaviour, as well as verifying that the cond parameter is applied which was previously not covered by test automation.

Checklist

The contributor guide contains information that will be helpful to first time contributors. There also are a few conditions for merging Pull Requests in Forgejo repositories. You are also welcome to join the Forgejo development chatroom.

Tests

I added test coverage for Go changes...
- in their respective *_test.go for unit tests.
- in the tests/integration directory if it involves interactions with a live Forgejo server.
I added test coverage for JavaScript changes...
- in web_src/js/*.test.js if it can be unit tested.
- in tests/e2e/*.test.e2e.js if it requires interactions with a live Forgejo server (see also the developer guide for JavaScript testing).

Documentation

I created a pull request to the documentation to explain to Forgejo users how to use this change.
I did not document these changes and I do not expect someone else to do it.

Release notes

I do not want this change to show in the release notes.
I want the title to show in the release notes with a link to this pull request.
I want the content of the release-notes/<pull request number>.md to be be used for the release notes instead of the title.

Release notes

Bug fixes
- PR: db.Iterate can miss records, can return records twice

**Backport:** https://codeberg.org/forgejo/forgejo/pulls/9657 Fixes #9644. Rewrites `db.Iterate` so that it performs DB queries in this format: - First: `SELECT ...columns... FROM table ORDER BY id LIMIT ...buffer-size...` - Subsequent buffer fills: adding a `WHERE id > ...last-id-from-previous...` This approach: - Prevents records from being missed or returned twice - Returns records in a predictable order - Should be faster, by virtue of using database indexes on the primary key to perform the query - Doesn't rely on any unpredictable database behaviour when using `LIMIT` and `OFFSET` without an `ORDER BY` - (Downside: does require reflection to read field values off Go structures for the primary key value) Expands the automated tests to include the predicted failure case identified in #9644, which verified the previous broken behaviour, as well as verifying that the `cond` parameter is applied which was previously not covered by test automation. ## Checklist The [contributor guide](https://forgejo.org/docs/next/contributor/) contains information that will be helpful to first time contributors. There also are a few [conditions for merging Pull Requests in Forgejo repositories](https://codeberg.org/forgejo/governance/src/branch/main/PullRequestsAgreement.md). You are also welcome to join the [Forgejo development chatroom](https://matrix.to/#/#forgejo-development:matrix.org). ### Tests - I added test coverage for Go changes... - [x] in their respective `*_test.go` for unit tests. - [ ] in the `tests/integration` directory if it involves interactions with a live Forgejo server. - I added test coverage for JavaScript changes... - [ ] in `web_src/js/*.test.js` if it can be unit tested. - [ ] in `tests/e2e/*.test.e2e.js` if it requires interactions with a live Forgejo server (see also the [developer guide for JavaScript testing](https://codeberg.org/forgejo/forgejo/src/branch/forgejo/tests/e2e/README.md#end-to-end-tests)). ### Documentation - [ ] I created a pull request [to the documentation](https://codeberg.org/forgejo/docs) to explain to Forgejo users how to use this change. - [x] I did not document these changes and I do not expect someone else to do it. ### Release notes - [ ] I do not want this change to show in the release notes. - [x] I want the title to show in the release notes with a link to this pull request. - [ ] I want the content of the `release-notes/<pull request number>.md` to be be used for the release notes instead of the title.  ## Release notes  - Bug fixes - [PR](https://codeberg.org/forgejo/forgejo/pulls/9657): db.Iterate can miss records, can return records twice

Rows
Columns