Forum OpenACS Q&A: Re: RFC: Avoid exposing object_ids in permanent URLs
I agree that the ambiguity is a problem.
Another way of fixing that would be to have a bug-tracker instance have a short pretty name, like "OpenACS Bug", or something even shorter, which can be used in referring to bugs, like "OpenACS Bug #123".
This would also be the string that you put in brackets in the subject line of email notifications going out...
OpenACS Bug #123: Here's what we tell you.
When you move a bug, well, you've moved the bug. Conceivably we could keep the old bug row around and clone it, and have bug-tracker redirect to the new, moved copy. The new one would still have a new number, though, as in "dotLRN Bug #73".
The only other issue is that DB vendors added sequences for a reason. There are serious performance implications to trying to do "roll your own sequences" and it's easy to either get wrong or create performance problems.
In the category of "getting it wrong" is the current bug number generation for bug-tracker which does this query in bt_bug__new:
select coalesce(max(bug_number),0) + 1 into v_bug_number from bt_bugs where project_id = p_project_id;to get the next bug number but does not do any locking to ensure that no one else ends up with the same number.
Locking the bt_bugs table is probably not an acceptable solution anyway. You could get a little better performance by having a table with (package_id, cur_bug_num) and doing a select for update (which would likely be fast enough for bug tracker for most sites out there but would not work well for a high traffic site with a lot of inserts (think forums for a site getting something like the traffic slashdot gets).
What are you left with? Sequences again only this time you would have a sequence or two per package_id. I think that is probably ok although you end up with a pretty cluttered db. Sequences mostly just work right although generally the tradeoff for avoiding locking is to sometimes have gaps in the sequence.
constraint bt_bugs_bug_number_un unique (project_id, bug_number)you could do the insert, and loop until the bt_bugs_bug_number_un constraint stopped firing (which would generally be one time through).
I am not sure how you do that with postgresql though and if that works in a transaction or not.