Menu
▾
▴
Re: [GUSDEV] PK constraints for version tables
|
From: Angel P. <an...@ma...> - 2005-10-24 13:30:03
|
OK, let's collect our knowledge: DB throughput is high enough to cause conflicts in version tables. Version tables are rarely used. Indexes/constraints of any type reduce transaction throughput. Given these three items, I think that steve's suggestion of using a sequence to order inserted rows, and debbies request for dropping any PKs in the version tables are a good way to keep track of data provenance and not make version tables a limitting factor on database population. -angel Michael Saffitz wrote: >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 >> >> > > > > >------------------------------------------------------- >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 > > |
