Re: Surrogate Keys: an Implementation Issue

Paul Mansour wrote:
> paul c wrote:
>

>> I don't see that immutability matters to the various purposes of most
>> db's if those identifiers aren't exposed.

If it's not exposed, then a db engine is free to mutate it however it wants. Some practical reasons might be to allow re-use of a finite range of values or to re-claim storage space, avoid having to write a garbage collector and so forth.

I noticed this in your reply to Bob Badour: "So for most tables it is not exposed, but for some it is." I'd say all bets are now off, doesn't this put us back where we started, ie. a system-supplied candidate key?

(BTW, as far as "track a row over time" goes, I'd say using real time-stamps has fewer problems than auto-increment values for which it can be quite hard to avoid duplicates if the incrementing is done by user code and the engine multi-tasks (which it likely will if disks are involved), eg., hard to avoid the "convoy phenomenon". Even with time-stamps, it's not clear to me that any OS guarantees different results for different time requests, eg., Posix used to specify a granularity of a hundredth of a second which wouldn't be fine enough on today's processors, so I imagine a real engine with a disk-based physical store would still need a serialized "hot spot" section to make sure the next time result isn't the same as the previous time. Also I think any system that promises auto-increment and also allows rollback is going to have a hard time avoiding 'gaps' unless it is allowed to mutate the auto-increment values.)

p Received on Thu Jul 20 2006 - 11:29:00 CDT