Re: [GUSDEV] PK constraints for version tables

[Chris S. <csa...@pc...>]

I was going to suggest (2) below, but will nitpick and point out that  
it would make the problem 1000 times less likely to occur, but that  
it's still possible to have collisions, especially if, say, 2  
processes are simultaneously working on overlapping data.
With (1), the problem is that with any collision, there is the  
possibility that you won't be able to determine in what order changes  
were made to data (which could, for example, break an automated  
"rollback" system). Given that collisions are probably already rare,  
and that algorithm_invocation_id helps distinguish simultaneous  
processes, this just may not be an important problem.
So (1) is still the easiest workaround, and I'd guess "good enough".
-Chris


On Oct 21, 2005, at 3:44 PM, Angel Pizarro wrote:

> Debbie's point is that uniqueness in version tables doesn't really  
> matter and it is getting in the way of fast inserts. The only ways  
> to fix this are:
>
> 1) drop the constraints
> 2) increase the precision of row_modification_date to milliseconds.
>
> -angel
>
> msa...@pc... wrote:
>
>
>>
>>
>> The problem with that is that the ID alone isn't necessarily  
>> unique-- a row that
>> has been edited multiple times will end up in the version table  
>> multiple times,
>> hence the addition of the modification_date to (attempt to) ensure  
>> uniqueness.
>>
>> --Mike
>>
>> Quoting Steve Fischer <sfi...@pc...>:
>>
>>
>>> yes, i guess so.
>>>
>>> steve
>>>
>>> Deborah Pinney wrote:
>>>
>>>
>>>> You mean drop the uniqueness constraint for the original PK 
>>>> +modification_date?
>>>> Steve Fischer wrote:
>>>>
>>>>
>>>>> how about just dropping the uniqueness constraint of the  
>>>>> modification date?
>>>>>
>>>>> steve
>>>>>
>>>>> Chris Sarnowski wrote:
>>>>>
>>>>>
>>>>>>
>>>>>> I can't speak for GUS needs in this particular case, but I've  
>>>>>> had the  same issue in other systems. Deborah is correct in  
>>>>>> that these fields  are not right for PK. The question is how  
>>>>>> important it is to preserve  exact order of the changes to the  
>>>>>> data. If it's not critical you  could just drop the PK. If it  
>>>>>> is (or even if it's not but you believe  in the rule "every  
>>>>>> table must have a PK"), then you should use a  surrogate key  
>>>>>> from a sequence. In practice, at least, the sequence  values  
>>>>>> increase monotonically so changes to a record that occur   
>>>>>> multiple times with the same modification date can still be  
>>>>>> ordered  correctly.
>>>>>> I prefer the latter, in general, but I suppose adding a new  
>>>>>> surrogate  key field to all version tables in GUS would be a  
>>>>>> pretty big deal.
>>>>>>
>>>>>> Just my thoughts.
>>>>>> -Chris Sarnowski
>>>>>>
>>>>>> On Oct 21, 2005, at 11:07 AM, Deborah Pinney wrote:
>>>>>>
>>>>>>
>>>>>>> I suggest that we drop the uniqueness constraint for PKs of  
>>>>>>> version  tables. PKs for these tables consist of the original  
>>>>>>> PK and the  modification date. Rapid computation can result  
>>>>>>> in uniqueness  copnstraint violations that are not remedied  
>>>>>>> by date format  changes. There doesn't seem to be a purpose  
>>>>>>> in having these  constraints for any of the version tables  
>>>>>>> and dropping the  constraints would not result in the loss of  
>>>>>>> information or change  query results. I don't know if there  
>>>>>>> is implicit indexing on these  keys but I wonder if this  
>>>>>>> could be handled using explicit indexing.