Re: Surrogate Keys: an Implementation Issue
Date: Thu, 20 Jul 2006 16:29:00 GMT
Message-ID: <gbOvg.213498$Mn5.58941_at_pd7tw3no>
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.
>
> I think the immutability matters if you are trying to have a rollback
> database or some built in audit support. How else can I track a row
> over time?
> ...
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.
(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 - 18:29:00 CEST