Re: UUID column as pimrary key?

On 01/05/2011 01:43 PM, Bill Moran wrote:
> In response to Rob Sargent <robjsargent@gmail.com>:
>>>
>>> In our case (and I expect it's the case with most people considering UUIDs)
>>> we're talking about independent devices that occasionally synchronize
>>> data between themselves.  These devices need to generate a unique ID
>>> of some sort without having to check with every other device.  This is
>>> one of the problems that UUIDs were intended to fix.
>>
>> Indeed there is a finite space.  A very large, but finite space, just as
>> sequence has I suspect.  If your software cannot handle a rollback for
>> whatever reason, you have much bigger problem on your hand than the the
>> remote chance of a collision in uuid generation.
>
> You missed the point.
>
> Despite the fact that the chance of a collision is very, very small, there
> is no easy way to fix it if it happens.  Zero.  It can't be done without
> shutting the system down, recalling all the remote devices and manually
> reconciling the problem ... which is not an option.
>
> Also, it's hard to DETECT the collision.  If a device creates a new record
> with a duplicate UUID, how do we tell that apart, during synchronization,
> from an update to the record?  Now I have your SSN or private medical
> data included as part of my record, which violates laws, could lead to
> incorrect medical care that could kill someone ... etc.
>
> So, despite the chance being very small, the consequences are HUGE.  I
> can't just detect it and roll back.
>
> If you have a single DB backend, these problem are easy to solve -- so
> easy, in fact, that using UUIDs is overkill.
>
> But when you have mission-critical data that must be correct and available
> under every circumstance, you have to consider that UUIDs are not a
> guarantee.  And when a method that DOES have a guarantee is one of the
> options, why would you take ANY risk at all, no matter how small?
>
> (BTW: I hope that the people who think that the risk is acceptable aren't
> writing medical software.  Even if it only kills one person every 10,000
> years because they were given the wrong medicine, that's too often in
> my opinion)
>

Seems the software would need to know whether an update or an add was
begin performed: is this a new patient or one all ready in the db.  New
record patient record and new id (of some arbitrary nature) and any new
subsidiary data associated appropriately.


BTW, you're opinion of efficacy of current medical practice is also out
of whack.  From a JAMA artical: "Similar to previous studies, almost a
quarter (22.7%) of active-care patient deaths were rated as at least
possibly preventable by optimal care, with 6.0% rated as probably or
definitely preventable. " That's 3 in 50. How d'ya like them odds?