Subject Re: [firebird-support] One Generator To Rule Them All
Author sugi
>>Now here's some crazy idea:
>>What if we just use one generator for all table PKs in a database?
> Not so crazy at all. Go for it!
Thank you ...:)

> I have done this on several databases. The only issue I see is whether your
> usage will likely cause the values to increase past the limit of the
> generator. Some simple estimating will probably convince you that this is
> highly unlikely.
Yes. Mr Daniel Rail suggested calculating a rough estimate of the
records needed, and here's the number I came up with :
Number of Records Per Second : 1000 (let's think big...:D, but
seriously, this is approximately maximum number of inserts per second
that i can push to my local server so far...:).
Seconds per day : 60x60x24 = 86400
Day per year : 365
Number of years in use: 10
'Unused Number' factor: 3 (assume one out of three generated numbers are
thrown out, due to rollback-ed transactions, etc).
Multiplied, the result is : 946,080,000,000. A bit out of range for
32bit integers, but this shouldn't be a problem for BIGINT.

> I also wrote some code to query each table which uses a generated PK to
> determine the highest value already used in the database. I will use this
> to reset the generator's current value in case of database corruption or
> other problems which make me lose confidence in the situation. I would
> recommend you prepare something similar. AFter all, you don't want the
> generator to start re-using values for some indeterminate amount of time
> before you stop it!
Thanks for the advice/warning. I think this is a very good point about
generator use that is almost never brought up.

> Finally, a nit-pick on "enterprise-unique": I would not consider these
> values as enterprise-unique, but only server-unique. I would expect an
> enterprise to maintain several, if not many, separate databases on
> different servers. So, the addition of a server-ID, or database-ID, would
> be needed to make the values enterprise-unique.
Good catch...:)
I might have to revisit the issue when replication comes into the
picture, though. Mr Peter Jacobi pointed out (thanks!) the UUID
available through UDFs, so I'll look into this one next. For my current
needs, 64bit integer should be enough.