Re: [GUSDEV] PK constraints for version tables

[Steve F. <sfi...@pc...>]

the "race" condition we have is that a single process is rapidly 
updating a row in time that is less than the resolution of our current 
date stamp.  we won't be able to distinguish them by 
row_alg_invocation_id as they are done by the same process.  it it 
likely but not assured that an increase in resolution will solve the 
problem. 

i propose that we:
  1. increase the precision
  2. drop the constraint
  3. don't really care too much

if we want to care, then, i suppose, we would have to add another column 
to the version tables that was an increasing sequence.

steve


Chris Sarnowski wrote:

> 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.
>>>>>>>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev