How can you auto-increment a non primary ID column scoped to another table?

Question

Let's imagine that we're building GitHub and we have two tables: repos and issues. Every GitHub repo has a collection of issues, and so the issues table has a foreign key of repo_id.

Now, when you're browsing a GitHub repo's issues, you don't want to be exposed to the internal id. Instead, you want something like a number, which increments from 1..n for only that repository. You want your first issue in your new repo to be numbered 1, not whatever the next id for an issue on GitHub is.

Of course, you need a way to increment, and you want to make sure that the number is unique when scoped to the repo. And so you especially want to avoid then any race condition where the same number can be generated twice.

What's the most straightforward way of handling this? A trigger? Something else entirely?

I am using PostgreSQL but would prefer approaches that are vanilla SQL where possible, e.g. triggers. If there's a simpler Postgres approach, then that would also be useful.

Any code that demonstrate your approach would be extraordinarily useful. Thanks!

Answer 1

I don't think there is a way to do this without the possibility of a race condition. This should minimize race conditions but not eliminate them. There may be better ways within specific database architectures. Assuming "REPOSITORY_ID" is provided by your application code:

insert into issues (repo_id,line_id) values (
    REPOSITORY_ID,
    coalesce((select max(line_id)+1 from issues where repo_id=REPOSITORY_ID),0)
);

This pulls the current highest line_id and increments it at the time of the insert. If there are no records, it defaults to 0. There is a small chance of a race if two inserts hit at the exact same time, but it seems unlikely. If you enforce uniqueness you can check for an error on insert and retry on failure.

Answer 2

Suppose you want to add a new issue to a certain repo, you could execute the following operations:

1. start a new explicit transaction;
2. select the repo you want to modify with a SELECT ... FOR UPDATE. This will put a row-level lock on it and prevent other transactions that want to add a new issue for that repo to proceed concurrently;
3. get the new issue number for that repo in some way (for instance you could have a latest_issue column in issue, as in one of the answers, or you could perform a query to find it);
4. insert the new issue with the correct issue number;
5. terminate the transaction: this will release the lock and allow other transactions that want to work on the same repo to continue.

So you could define a stored procedure in this way, and call it every time you want to insert a new issue. Under the hypothesis that there are not too many concurrent transactions trying to insert new issues for the same repository, this would prevent race conditions and still operate with a reasonable efficiency.