API: Allow setting the issue number when creating an issue #3749

GiteaMirror commented

@lunny commented on GitHub (Aug 8, 2019):

@argv-minus-one I think we should not do that. We have a migrations package https://github.com/go-gitea/gitea/tree/master/modules/migrations and we can migrate almost all data from github. Users can contribute this package and migrate via Gitea API.

@lunny commented on GitHub (Aug 8, 2019): @argv-minus-one I think we should not do that. We have a migrations package https://github.com/go-gitea/gitea/tree/master/modules/migrations and we can migrate almost all data from github. Users can contribute this package and migrate via Gitea API.

GiteaMirror commented

@argv-minus-one commented on GitHub (Aug 8, 2019):

I see.

I'll take a look, but I'm not sure if I have time to learn Go and Gitea's internals right now. In the mean time, I've posted issue #7802 for my specific use-case, which is importing from Bugzilla.

@argv-minus-one commented on GitHub (Aug 8, 2019): I see. I'll take a look, but I'm not sure if I have time to learn Go and Gitea's internals right now. In the mean time, I've posted issue #7802 for my specific use-case, which is importing from Bugzilla.

GiteaMirror commented

@argv-minus-one commented on GitHub (Aug 9, 2019):

The migrations package seems designed for migrating Git repositories and associated data from GitHub-like sites, and would need a pretty major redesign to handle migrating anything else, such as issues from a separate issue tracker like Bugzilla.

@argv-minus-one commented on GitHub (Aug 9, 2019): The migrations package seems designed for migrating Git repositories and associated data from GitHub-like sites, and would need a pretty major redesign to handle migrating anything else, such as issues from a separate issue tracker like Bugzilla.

GiteaMirror commented

@lunny commented on GitHub (Aug 9, 2019):

@argv-minus-one I never used bugzilla and I want to know why do you want a special issue number?

@lunny commented on GitHub (Aug 9, 2019): @argv-minus-one I never used bugzilla and I want to know why do you want a special issue number?

GiteaMirror commented

@argv-minus-one commented on GitHub (Aug 11, 2019):

I want to preserve the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”).

@argv-minus-one commented on GitHub (Aug 11, 2019): I want to *preserve* the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”).

GiteaMirror commented

@guillep2k commented on GitHub (Aug 11, 2019):

I want to preserve the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”).

This looks like a one-time procedure. I'd rather write a script to generate the UPDATE instructions while migrating and run it right after finishing the process. If you do this, remember to do it in strict descending order, otherwise you'll have ID number collisions. After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know).

@guillep2k commented on GitHub (Aug 11, 2019): > > > I want to _preserve_ the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”). This looks like a one-time procedure. I'd rather write a script to generate the `UPDATE` instructions while migrating and run it right after finishing the process. If you do this, remember to do it in strict _descending_ order, otherwise you'll have ID number collisions. After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know).

GiteaMirror commented

2025-11-02 05:24:04 -06:00

@lafriks commented on GitHub (Aug 11, 2019):

I don't see how this could be implemented without breaking a lot of stuff imho

@lafriks commented on GitHub (Aug 11, 2019): I don't see how this could be implemented without breaking a lot of stuff imho

GiteaMirror commented

@jpicht commented on GitHub (Aug 11, 2019):

I don't see how this could be implemented without breaking a lot of stuff imho

It could, by requiring that the override-issue-number be larger than every issue number already used for this repository. One should limit this ability to the repo owner, obviously.

@jpicht commented on GitHub (Aug 11, 2019): > I don't see how this could be implemented without breaking a lot of stuff imho It could, by requiring that the override-issue-number be larger than every issue number already used for this repository. One should limit this ability to the repo owner, obviously.

GiteaMirror commented

2025-11-02 05:24:04 -06:00

@argv-minus-one commented on GitHub (Aug 13, 2019):

[@guillep2k] After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know).

I don't see any sequence for the index column (containing the issue number) in Postgres. When Gitea goes to create a new issue, it finds the next issue number using a SELECT MAX(`index`) query, rather than calling a sequence manipulation function. Neither Gitea nor the database seem to keep a stored “next issue number” anywhere. Please correct me if I'm wrong!

[@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho

What would break?

[@jpicht] It could, by requiring that the override-issue-number be larger than every issue number already used for this repository.

That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a UNIQUE INDEX named UQE_issue_repo_index to ensure that all issues have a unique issue number.

@argv-minus-one commented on GitHub (Aug 13, 2019): > [@guillep2k] After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know). I don't see any sequence for the `index` column (containing the issue number) in Postgres. When Gitea goes to create a new issue, [it finds the next issue number using a ``SELECT MAX(`index`)`` query](https://github.com/go-gitea/gitea/blob/c662f4ced8cdbfd6865271af3cdd0bea3492c4e2/models/issue.go#L1045), rather than calling a [sequence manipulation function](https://www.postgresql.org/docs/11/functions-sequence.html). Neither Gitea nor the database seem to keep a stored “next issue number” anywhere. Please correct me if I'm wrong! > [@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho What would break? > [@jpicht] It could, by requiring that the override-issue-number be larger than every issue number already used for this repository. That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a `UNIQUE INDEX` named `UQE_issue_repo_index` to ensure that all issues have a unique issue number.

GiteaMirror commented

2025-11-02 05:24:04 -06:00

@guillep2k commented on GitHub (Aug 13, 2019):

[@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho

What would break?

If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a select max(id) will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision).

It's pretty buried in the docs, but SQLite is suggested only for tiny installations.

@guillep2k commented on GitHub (Aug 13, 2019): > > > [@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho > > What would break? > If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a `select max(id)` will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision). It's pretty [buried in the docs](https://docs.gitea.io/en-us/install-from-source/), but SQLite is suggested only for tiny installations.

GiteaMirror commented

@argv-minus-one commented on GitHub (Aug 13, 2019):

[@guillep2k] If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a select max(id) will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision).

I see. Well, Gitea contains a SELECT MAX query here, which is called from here when creating a new issue, to decide what the new issue number will be. It does not appear to care which database is in use. If this is a performance problem, then perhaps it should be reported?

Anyway, I threw together a Gitea instance on PostgreSQL, then tried creating an issue, increasing its issue number (from #1 to #2) with an UPDATE, then creating another issue. It creates the second issue without complaint and makes it #3, same as on SQLite. So, that doesn't seem to break.

Is there anything else that might break when I do this?

Note, by the way, that the issue table contains two auto-increment-like columns: id and index. id is globally unique, and is a normal auto-increment column. index is unique only for a single repository, and starts at 1 for each repository (that is, each repo's first issue is #1, its second issue is #2, and so on).

Normal database auto-increment can't be used for this. PostgreSQL sequences don't seem up to the task, either, unless Gitea were to CREATE SEQUENCE for every single repo on the site (that is, one CREATE SEQUENCE for each row in the repository table).

I only know of two ways to deal with this: something along the lines of SELECT MAX or SELECT … ORDER BY … LIMIT 1, or by querying and immediately updating a column in the repository table for the next issue number (which may not be any faster).

PostgreSQL seems to prefer SELECT … ORDER BY … LIMIT 1, by the way. EXPLAIN ANALYZE SELECT MAX(index) FROM issue WHERE repo_id = 1 yields this:

 Aggregate  (cost=8.16..8.17 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)
   ->  Index Scan using "IDX_issue_repo_id" on issue  (cost=0.14..8.16 rows=1 width=8) (actual time=0.017..0.019 rows=2 loops=1)
         Index Cond: (repo_id = 1)
 Planning Time: 0.244 ms
 Execution Time: 0.061 ms

Whereas EXPLAIN ANALYZE SELECT index FROM issue WHERE repo_id = 1 ORDER BY index DESC LIMIT 1 yields:

 Limit  (cost=0.14..8.16 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1)
   ->  Index Only Scan Backward using "UQE_issue_repo_index" on issue  (cost=0.14..8.16 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
         Index Cond: (repo_id = 1)
         Heap Fetches: 1
 Planning Time: 0.158 ms
 Execution Time: 0.041 ms

If I'm reading this right, PostgreSQL answers a SELECT MAX query on an indexed column by scanning the entire index, rather than just fetching the highest value from the index as with SELECT … ORDER BY … LIMIT 1. The actual execution time is slightly faster, but I have only one repo with two issues, so I have no idea how these queries perform on a real-world Gitea database.

@argv-minus-one commented on GitHub (Aug 13, 2019): > [@guillep2k] If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a `select max(id)` will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision). I see. Well, Gitea contains a `SELECT MAX` query [here](https://github.com/go-gitea/gitea/blob/c662f4ced8cdbfd6865271af3cdd0bea3492c4e2/models/issue.go#L1045), which is called from [here](https://github.com/go-gitea/gitea/blob/c662f4ced8cdbfd6865271af3cdd0bea3492c4e2/models/issue.go#L1045) when creating a new issue, to decide what the new issue number will be. It does not appear to care which database is in use. If this is a performance problem, then perhaps it should be reported? Anyway, I threw together a Gitea instance on PostgreSQL, then tried creating an issue, increasing its issue number (from #1 to #2) with an `UPDATE`, then creating another issue. It creates the second issue without complaint and makes it #3, same as on SQLite. So, that doesn't seem to break. Is there anything else that might break when I do this? --- Note, by the way, that the `issue` table contains *two* auto-increment-like columns: `id` and `index`. `id` is globally unique, and is a normal auto-increment column. `index` is unique only for a single repository, and starts at 1 for each repository (that is, each repo's first issue is #1, its second issue is #2, and so on). Normal database auto-increment can't be used for this. PostgreSQL sequences don't seem up to the task, either, unless Gitea were to `CREATE SEQUENCE` for every single repo on the site (that is, one `CREATE SEQUENCE` for each *row* in the `repository` table). I only know of two ways to deal with this: something along the lines of `SELECT MAX` or `SELECT … ORDER BY … LIMIT 1`, or by querying and immediately updating a column in the `repository` table for the next issue number (which may not be any faster). PostgreSQL seems to prefer `SELECT … ORDER BY … LIMIT 1`, by the way. `EXPLAIN ANALYZE SELECT MAX(index) FROM issue WHERE repo_id = 1` yields this: ``` Aggregate (cost=8.16..8.17 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1) -> Index Scan using "IDX_issue_repo_id" on issue (cost=0.14..8.16 rows=1 width=8) (actual time=0.017..0.019 rows=2 loops=1) Index Cond: (repo_id = 1) Planning Time: 0.244 ms Execution Time: 0.061 ms ``` Whereas `EXPLAIN ANALYZE SELECT index FROM issue WHERE repo_id = 1 ORDER BY index DESC LIMIT 1` yields: ``` Limit (cost=0.14..8.16 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1) -> Index Only Scan Backward using "UQE_issue_repo_index" on issue (cost=0.14..8.16 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1) Index Cond: (repo_id = 1) Heap Fetches: 1 Planning Time: 0.158 ms Execution Time: 0.041 ms ``` If I'm reading this right, PostgreSQL answers a `SELECT MAX` query on an indexed column by scanning the entire index, rather than just fetching the highest value from the index as with `SELECT … ORDER BY … LIMIT 1`. The actual execution time is slightly faster, but I have only one repo with two issues, so I have no idea how these queries perform on a real-world Gitea database.

GiteaMirror commented

@guillep2k commented on GitHub (Aug 13, 2019):

@argv-minus-one You are exactly right, as I was getting confused with the ID property which is in fact auto-incremented, but the index is not, apparently. I'm sorry about that.

@guillep2k commented on GitHub (Aug 13, 2019): @argv-minus-one You are exactly right, as I was getting confused with the `ID` property which is in fact auto-incremented, but the `index` is not, apparently. I'm sorry about that.

GiteaMirror commented

@jpicht commented on GitHub (Aug 13, 2019):

That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a UNIQUE INDEX named UQE_issue_repo_index to ensure that all issues have a unique issue number.

If somebody deleted some issues, for whatever reason, there might be commits referencing the issue. If somebody recycled that issue number, it could become very confusing fast.

So as your use case does not require for them to be inserted out-of-order, this could be a reasonable restriction.

Btw. temporarily creating issues with the desired index - 1 in the database while importing the issues from bugzilla would result in them being created with the same issue number. It's a dirty hack, but it would work.

@jpicht commented on GitHub (Aug 13, 2019): > That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a `UNIQUE INDEX` named `UQE_issue_repo_index` to ensure that all issues have a unique issue number. If somebody deleted some issues, for whatever reason, there might be commits referencing the issue. If somebody recycled that issue number, it could become very confusing fast. So as your use case does not require for them to be inserted out-of-order, this could be a reasonable restriction. Btw. temporarily creating issues with the desired index - 1 in the database while importing the issues from bugzilla would result in them being created with the same issue number. It's a dirty hack, but it would work.

GiteaMirror commented