On Monday 14 July 2008 04:05:43 Michael Bayer wrote: > On Jul 13, 2008, at 1:47 PM, [EMAIL PROTECTED] wrote: > > hi all > > i have a tree/graph of nodes and links inbetween (= explicit > > m2m), and many users can simultaneusly change nodes/links. > > i need to get all-reachable-from-x in one query - so i'm saving > > also all the paths (not just the immediate neibourghs). i think > > this was called nested-sets or whatever. > > The question is, (how) can i do that concurency-safe. > > is version_id a viable/usable solution (can it happen that some > > poor guy is never able to save his stuff as others always > > overtake him)? > > nested sets is awful for concurrency since any node additions or > deletions require a full UPDATE of at least half the table's rows. > But when you say "saving all the paths" that sounds more like > materialized path. Which I haven't used in a super long time but > also would require many large UPDATEs if the tree structure is > changing. Depending on what kind of performance you need you can > maybe lock the whole table, or queue all the updates/deletes into a > single thread/ process..otherwise you might want to look into > decreasing the amount of interdependent rows if you want to use > version_id or row locking. These are just guesses though without > much deep thought put into it.
hmm this doesnt sound very hopeful.. i dont really need huge performance as it would be just users (say 20-30 max) touching those. i'm trying different ways to solve my riddle, so here further explanations, and some thinking aloud. i dont really need the tree itself, but the values associated with each node, via another m2m. and these are bitemporal. There is no real update and deletion, they become insertions of new versions - of the associations and (nodes and/or values). There is an "inheritance" between the node-values, children inherit parent's value if not having own. So i can/want to flatten the _resulting_ set of values for each node (not just it's own but also those that have come from the parent etc till the root), to be able to get all values associate with particular node, as one query. So any change to the tree or to the values of some intermediate node will result into (massive or not) insertions of new associations, flattening the result-set for each child node. e.g. A,B,C are nodes, B,C inherit A A: x=1,y=2; B: x=5,z=6; C: y=7,z=8 flattening results in A: x=1,y=2; B: x=5,y=2,z=6; C: x=1,y=7,z=8 let user1 change A.x to be 9. this results in flattened sets as A: x=9,y=2; B: x=5,y=2,z=6; C: x=9,y=7,z=8 (new values in A.x and C.x, =9) while doing this, another user2 changes it to x=0, and y=3: A: x=0,y=3; B: x=5,y=3,z=6; C: x=0,y=7,z=8 (new values in A.x= C.x=0, A.y= B.y=3) transaction-wise i can ensure (via revision-id instead of trans-timestamp) that the two set of inserts dont mix, so one will either get the first set or the second set but not some cross inbetween. So it's a sort of last-started wins. Still some change by user1 may protrude through, if not covered by user2 changes - and i'm not sure is this wrong or right. maybe it's ok, maybe not; depends if the changes within one set are independent or not (say user1 changes the first name and the family name, user2 changes second name and family name. if that's fixing typos, ok; if its complete change, not ok). . probably such thing cannot be assured at all, or the system has to be single-user. i dont know how if i can or whether i need to do "version_id"-like checking here - seems like asking if last version is still the one we started with. But between that query and the following Insert someone may stick another value... the inserts has to be conditional and fail if the last-value is not what was supposed to be... which seems awfuly entangled. on rereading above 2 paragraphs, this is called "merging" in svn-terms. Maybe even this can be assured if the revision-set includes old unchanged values too as-they-were. hmmm. In this case though, user1 will never see his changes, as user2 did his later. maybe rise a "conflict", again as in svn terms? now, changes in the tree-structure (not just values) will definitely has to be serialized somehow, but i hope these will be rare enough. svn-parallel of these is moving files/directories and svn isn't very good at it... but bzr is. maybe i should look how they do it.. any comments? svilen --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
