Nothing I saw that said int could not become bigint.
My bad. The code cannot be a bigint. Or it could be a bigint between 1 to 99999999 :)
I thought it was not important. The code could be a number from 1 to 99999999 (so an Int will be OK) assigned in order-ish. This is because of business requirements. The code should not change in case a row is deleted. That rules out using windowed functions. At least for this problem. There could be some gaps if they are not too big (similar to a serial column when a transaction is rolled back). We are more concerned about code generation collisions (for example 2 transactions calculating the same code value) than gaps. For each pair (group, element) the code, once assigned should remain constant. This is because the group, the element, and the code will become an id (business-related). So:
group, element, code = bid
1, 3, 1 = bid 131
2, 1, 1 = bid 211
etc
This calculated bid follows the convention described here and it is used as a reference in other tables. Therefore it should not change.
All this weirdness is because of business requirements. I am good with a classic serial column. But that will not map our business rules.
Regarding to the implementation of this. Our concern is to choose something not slow (it does not need to be super fast because there will be more operations in other tables using the same transaction) and I thought that creating/dropping sequences could be a solution. But I was not sure. I am not sure how it will behave with millions of sequences.
If there is another solution different than millions of sequences that do not block, generate few gaps (and those gaps are small) and never generate collisions then I will happily use it.
I hope I described the problem completely.
Pablo