Re: [GUSDEV] PK constraints for version tables

[Michael S. <msa...@pc...>]

What does the VERSION_TRANSACTION_ID column track?  Perhaps we could take
advantage of this for a composite PK, although I suppose that it may not
work if there are multiple edits to a given row within a transaction (which
is not well defined here).

--Mike



On 10/24/05 7:32 AM, "pi...@pc..." <pi...@pc...> wrote:

> The date format was modified (precision increased) and the problem persisted).
> 
> The version tables are useful for a few reasons:
> 1) Rows can be restored that were updated or deleted in error.
> 2) There is a record of who, when, and how a row was updated or deleted.
> 
> Restoration can be accomplished using the alg_inv_id and as long as the
> user_id
> and version modification and original modification date are available, the
> modifications can be tracked.
> 
> I'd like to point out that the version tables are rarely used.
> 
> Quoting Steve Fischer <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
>> 
>> 
>> 
>> -------------------------------------------------------
>> 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
>> 
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by the JBoss Inc.
> Get Certified Today * Register for a JBoss Training Course
> Free Certification Exam for All Training Attendees Through End of 2005
> Visit http://www.jboss.com/services/certification for more information
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev