Merken

Why UPSERT is weird

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
and this is my name is spelled the
date and this is my talk and y absurd we're at which seems like graphs this site provides a vital but it really wasn't intended to be and I think I'm going to survive in characterizing it as weird and and I was ready made a contributor I've been workin performs both historically and I work for brokers and learn about what's this talk about so and this concerns absurd which is then this notion that defined of an insert on updates so long as the people to get 1 or the other of appropriate and as defined from the late you because in the case of my particular definition of service a would be a violation of index very unique and the and the probability of the different stations most of the bottom of the implementation of the law I'm not aware that there is a little MIT of the at 1 voice that further as that of it as so yeah that's pretty of the country or go out but anyway so
I would also like to wrestle with the what is not about and so actors in the room will be aware that there was some robust discussion on lists about and then into the details of that and Michael commend implementing this particular at taking kindness who was an reviewer of and so this work and but I should go different way and which is essentially an open issue but um it's kind of relevant to this special day and anything I say here would probably to solve to what in the direction that you want me to go so we're talking about implementations that are more more and more masculine from that that will 1 you answer from gender that usable semantics so now I'm being going about In many feature and this on the back man and I wanted to point go to the that satisfy
everyone and so actors you know and are aware that this is the new feature on and the ice aware that at communities advantage for not having which fair and users monthly broadly useful and something that will you do will accomplish what what they want with the minimum fuss down there's is a lot of misinformation on the Internet at large about what works and what doesn't and there's a lot of them is not the suggestions members blogs and so on and it's that he might go about implementing this in and Hartford way which are incorrect and they don't consider various recognition so I am I quite obviously the vendor I want you all satisfied with here and so this is the random
sampling of and people and only about this on Twitter and take it for what you well it's not assigned to this whole that's and treating it cranks complaining about any number of things on there so this is just a visual metaphor for events I hear all the time they were are the best but in
theory the Mongols bills for observed
in PostgreSQL work in in particular well my main 1 is I want to preserve while the 1st is the fundamental to a property which is say recommit isolation there will always get atomic insert or update as appropriate and there can be no 1 principle deadlocking of deadlocking that we as a community can well you know pledges with groups deadlocking and we can't well just don't absurd so much emoticons muttering wanted me if they're doing something of directly instance where the calculus analysis say well this is you know the mutual dependency to sort that the concept of them the option of and also know spheres unique constraint violations which is pretty much the same thing and this was unconcerned leaves and so my mind a command we might provide would would not require us to specify index in the analysis and there being implicit understood as a unique index of emerging on and usually required to get those details right it's almost always self-evident what they intend to Virgil and we don't want requirements specified in index that we don't have obvious problems and several grow acceptable performance particularly don't burn threats these excessively quickly and thought to be intractable for and birds use cases and particular and use cases around a bit resolution for a multi-master replication which is something I have become increasingly important in the years ahead so this is Michael's now many go on discussing
interconnected work breast within the executor we handled and certain single-table slot In this exact insert function so we FIL each time for every given multi statement a multi-row inserts analytical for every row you want start and what occurs within reducing he thought was inserted and which we the physical identifier for and subsequently to the 1 that we and the IndexTable themselves now have a reference to eat and there a table that they can and the other containing use found so I a present only the reactors method is unique indexes and cataloged by and community will equals true and so what happens if if there is a unique inequality the treecode work as actually and because they always and it might happen on 1st connect much of the subsequent 1 and m in there it's the aliens promised those 1st
and but in practice is only that 1 so more less think about what about the trees without enacted in practice and I had to be trees sented itself and so with the exception they don't have the visibility information so this becomes necessary for for the good the get that is not evidence from together physical uh IndexTable as to whether or not it ought to be visible to R and the 60 snapshot and but when handling and potential death violations we don't actually care about is the semantics anyway and we can me quite think what we still don't worry about things that are not visible to our transaction what are conclusively committed but actually what we're looking for their and so this is practical inverting snapshot now existing in the sixties and of course and and these are used for just this kind of thing and then a number of different code for these conclusively visible pockets and we may have to wait thing enactment of the transaction that Eritrean started if it's an running concurrently own and and others have from there if there is 1 discipline committed
and along the tree structure here the altar locks the physical structure of the tree is preserved various invariant the emphasis when it's losing here this is then the locking protocol is more or less difficult and whatever they can be 3 implementations have used and the data structure as opposed to serving some and higher-level purpose that makes sense only in the relational database away and these are from preserving varied various invariance if you like is this will be on at work in the next 10 and so unique index kind of sort of work by treating the uniqueness aspect as just such an invariant in embodies course it's lot messier that and we could have multiple versions of the holes in the same B-tree pursuing each new version around and they don't do begin to the according to Monday's we find useful here and so they could be deleted already or something like that in itself physically the presence of course we have to cover this sort of distinction and so what what occurs in order to when were inserting a IndexTable into each physical index is that we and excessive block and a least page and subsequent to that research people duplicates and on the 1st and the 2nd the pages and and the originals lock until the trip so we will consider this between question questionable practice but that's the way it works and we need accessible Pollock because we can't very well in the shared nest to extensive so we need to sort of and you like and but in in their values were considering uniqueness that whatever it all European sorting into index and we must limited sense LOC said integer 5 value in the abstract and we cannot locks in existing object because there isn't any that's the point where the problem we help least and so
I've occasionally characterized this as value knocking on so into phrase walking systems this isn't something that can appears much more frequently in is recognized by body heavyweight lot manager of such systems and more natural kind of and concept to people use those systems and so units of and so the network were were doing this in order to prevent race conditions become very well if someone to get in ahead of us if you like mn
inserts a million we are expecting for uniqueness at that time and we can very well that accessible block all along or it will enhance and display nature but blocks down only in acceptable for numerous reasons and so once you visit inconclusively candid confortable and originally from transaction hasn't been reported other don't know what's going happen with exactly of course with the weights C is the all day long temporal hold that book the the having obtained the non-transactional action IT of send for the transaction and we can will go sleep on that and weight until it minutes and then we will cannot and then we can check yet free which from scratch and and then we'll do that within of yes no answer in respect of question is there a would be is there would be in this unique and so in a sense we link the animal level force both a lot longer than that of the mind for conduction blocks in this in this way and we don't have rollocking style lock arbitration and in the tree for at present so when this happens on there is no guarantee that the 1st person to weight will be based in the 1st person at a 2nd chance if you like and unlike say rollocking where that would be acceptable and and this works OK for unique and that's important and the rest of the very limited formed by locking up enough for a unique index of what about the naive approach
absurd and this is thoughts on how what kind of preventive acting so we in a sense quite indexed by locking to conventional reluctant we don't insert the interval 1st we don't value loss all unique indexes whatever mechanism of might actually be and then having perhaps due to consensus Baconian search an otherwise we going that and and then finally released by lots only done with the operator encircling among the and then this escalation occurs and everything works out right well known there are problems with this and and that brings me to observe in practice so it's this
is a kind of visual metaphor of 4 I was trying communicate which is that when you look at absurd it looks somewhat like this and possible at the museum look at those you realize looks more like this and which is to say that not terribly impressive in many systems as implemented an and of 1 interconnected problems are referred to with overlapping value in real life
so if the user lots 2 things at once and then not care about the order is a very general statement that the net cost deadlock and so the 2 distinct types of locks value laden relax this as an equally applies to any proof of concept implementation of produced by any 1 day and so if we don't have that will and see and consistent locking in way that doesn't sensible country well tell users well known observe so much sorry they something so as I as received still forward a regular answers in the the out of serving and and there only going prevent it from finishing their inserts because of course the insertion indexes last and that's it and and we can we determine predicates are public on and this is thought to be quite impractical and that's the NP-hard problem so my conclusion and is that you cannot find dialoguing for a lot and had the 2 overlap on the assumption that everything working inviable to and that we have to sort of just get you know get get 1 locking in the lock in and thing out of that just isn't going to work acceptably provided we assume that these RecMotif camper will just throw an error of any kind we want 1 or more or less similar semantics to the crude have sort of way we do have long which is ideal for a number reasons where we have and basically we inserted in this work we update you to those can both be that when the others can fail safely release with and so we want to provide essentially the same guarantees about which is and work in a reasonable it is revealing recommitted 3 ought to not grow any kind of error and I think what what these occupations in this area are and I don't know that I mapping into a much on assessing the burning of the cases that are important and and those could be covered by a number of different ways but I think this is at the most compelling case because when talking about all the systems now and there their merge implantation is a website and telling problems with the implantation sequel server and it's kind is is that a messy business when you're the mean you already saw the yeah the the visual metaphor used for how out of sort works in practice I think the way the way works in practice is a cut because the kind of kind of cheap and it's kind of kind of analysis also necessary to consider the tradeoff of the systems that made use of the nature of this kind of thing and and in particular with that we assume that SQL merge other systems would provide the same guarantees that describe and when I 1st just my requirements for observed everyone always assume that labor is most people that it would do you would do and then insert or update impact is not the case the whole state
durations implementations you look at the violation errors that were perfectly simple and absurd search style as you always possible to get on with the violation errors without um misusing the feature anyway under concurrency and so you'll randomly have long when we have these transactions growing areas I would suggest that this is unacceptable and it's very felt solving basic problem and the reason that we might want to do that thought and that is not my immediate concern so the author of blog right I just saw the show describe the issues and describes how this this is intent of flock M and the uptake he M and and is a picking up as well and and it's a releases the locks wire doing answer and is it is somewhat like my value Lux M. and more credit cards use hints or to make those value locked persists till the end of the transaction they that it should go I presume it's often fall because it would cause on principle that locking which is another problem you could have and so this is this whole business this is rather physically and all the 2nd I have considered to be an acceptable for solving this problem so and there is something we learned from the cycle is I have no idea for my proposed patch
those here and back to what you and highlights the value of so we have a syllable Pollock's that are already acquired in the process of painting area of the object of the present in the connective force we as we currently do it grants exclusive lock on link between the ages of both Apple escalate this to a heavyweight loss and in respect of each index or entering into and the goal here is to reach consensus across unique indexes analyze and certain that it wasn't really clear implementation what the user means but it's really really obvious to the user what they need they know what they mean physically among the edge cases and true in those have their considering but but in the general case it's self-evident as to what an index you connect they expect their uh violation might beyond so we put because we don't we don't know that Implicit so we need to reach a consensus across all indexes have my produce essentially stagger what is in fact the traditional union connected works across multiple the indexes in an attempt to reach such a sensors if not I go to lock do look at row opportunistic when mean by that well I mean that just like when were doing regular unique index assertion there's no way and did the lock arbitration rules are ill-defined was once again there's no guarantee that the 1st inserted the 1st weighted and the the first one against that cancer there is what you might expose characterizes rates and this is also a property of my implementation I would suggest it's an innate property of anything that has the and the various trade off the light proposed as being the most useful for our 1st run this thing but when we go lot more say having already released block together lot row optimistically and so the racial heavily pages are dropped we go to update otherwise we risk principle deadlocking between the OK something so that's kind of like SQL Server except there are theoretical locks formation has it's because we're looking were not accepting it if there is and failed answer Philip which appears to be what the proprietary implementations of words do we all knew that the SQL standard require merge to deal with these and various cases but it was always my assumption that and of little fretting reason in fact that sound that was because the you know they didn't want have to go into details of concurrency model that they would typically not want discussed this in purposely in those aspects will find that's why I thought it was a case that there was no requirement that they didn't have adverse conditions but in fact it seems like they have and In any case they be felt and meet my definition of of the what being felt had what was called the fundamental absurd property anyway yesterday's locks originality because we are living in additional and were were not making useful progress this is analogous to what occurs today with uh when we have you know we have a healthy yes he well example were really thing similarly there's no and the reason to think that in theory could could loop indefinitely and followed and it has not been issued practice 10 so this is it is a tradeoff essence Wilkinson
conclusions here and what kind of confusing that roles and he rose that its rows and tables and can a kind work like value lot just by virtue values if and only if there is a physical next couple so it's kind of confusing that you have these uh the prose in tables that are in 1 sense by firelocks and that you can block on a value level conflict and so on he to make those that distinction and I think it does that it still fairly distinction when you you know regardless of implantation as we'll see and learning and reason acquire to s you start off with different trade off to the 1 by far and so this is been trying to call somebody that all basically I think this view has them 1 acceptance on actors and so Paikea them in response to and from the that that I had on his and sketch that the take of data is an approach to account for this fact and so it's a little bit and fuzzy as to whether or not he would call what I'm calling value firelocks is a semantics I personally find it useful because you're you know that the important thing here is that you can lock something that doesn't exist yet which is a course not the case with the role of and and for the value that from the school when you go to insert and they something from Beijing and stop it from starting in mean so on the low side on the
proposed syntax and this is something that is probably by controversial and but is negotiable and so on and it looks more like
this on the part of ways so so so anyway on the initial motivation for doing things this way was a lot of useful for a multi-master replication cases ticular which were an upfront concern that I had undress informs me that he feels that it seemed like I will be found this is useful and so I'm willing to and certainly willing to revisit the syntax proposed here and it isn't all that isn't all that interesting except perhaps it's better that it's more flexible than other approaches that we might take if for example we were accused so my sequels insert under the heat OK syntax is less but more than that because you can delete something in that opening and and things like that fundamental which means you have that's what they want right I guess so what I'm just on saying is that in practice everyone else and they have something like that they don't have the mass which planning and so it is isn't it's not like when someone more flexible Lewis less and we can actually detect where conflicts current going to the image itself and so this is because the view that corruption so familiar with the rest of the domain of the talk is not as is it is a arrival city here so when starting in this table test if you like opportunistically and duplicate were projecting that out using the special rejects also this is uh this is a temporary on table expressions from his reference near there were was objecting rejects them on you take those that are rejected Jordanian some so and that's this is this is a like builder unobserved things possible best idea of what the very out and this has interesting implications in cells which would equally well applied to of this in fact I think and and over now
this is only in the large system progress conflict with problem that in however has was
present no but the person that initially identified the problem here is what he thinks about them so that again at the same time here we
consider that we can insert something that could be to look at but you get that isn't visible to ah transaction pressure rather a snapshot and because it logically occurs later if you will we still care about them do something about it the question becomes what and now Robert suggests perhaps not entirely
seriously that sound you have a serialization that and even I like that and that seems like it's another thing that I would of an alphabet if you want if you if you want read can you know have serializable translational use relies places you don't end up and so you know we have to worry about whether or not something we might have got there isn't even visible to our snapshot which is obviously problematic and sums it is
really wanted name we need have paid succeed because it doesn't see the thing it needs to update then it's not going to work if they will obtain anything even though it ought to have released within the developers mental model of the quite counterintuitive will happen so would hope what found so that's that so the thing is set back from it and then briefly discuss the about on all mechanisms so but present would that recommend isolation 0 obtained lead statements to be like reach in the future rather than have sterilization fire and 1 of the open concurrently committed and actions of other people to be updated quarterly so in a sense were already playing tricks were reaching in the future in order to avoid having sterilization failure this is the historic behavior of recommended by the huge amount mechanism make this work it's rather involved and just for the can allow and reasoning 1 for each modified town but ultimately the reason this happens is you have a better idea that this was the unintentionally do something and this is what we do so to
illustrate with a simple recommitted update and ex-con it's a modified they will note here is explain analyze reason that statement when the exact date the findings of the mid exact insert call 1 modified cable knowledge and this is where the magic may happen and that is special IndexSpan elements using ordinary in the sixties snapshots who we can reach in the future alright but we need something to grab and present to get there actually and them in the summer of origin and that is different to my problem where there's end product preparation no rovers M so this is something that has been called in in the CC violation and in that its widening intuitive notion of how and this is your work and this is something that occurs all the time today with the the effects of recommitted level hold back it's it's a very very complicated mechanism on any of the details of how much well what is
being corrupt so over unintelligible was sterilization an unusually reasoning unsurprising instead then something similar for and my problem press reading of new and this is violation a new way of reading and future sufficient to serve the needs of absurd sounds so the idea here as implemented is that sound not couple that is likely in future and therefore not there's 1 national rules becomes visible simply by virtue of your having opted with an upstairs which is kind of strange all branches um but it's basically a it's a mechanism for looking into the into the future and to the degree necessary to make uh looks at work and it doesn't really have individual prose syntax and if you were to pass that around internally it's still thought someone asked of in my view I want I don't presume too much that and what this means for the implementation but and I'm trying to give a relatively high level overview of some problems I see them so I'm certainly is a
fundamental tradeoff appears to me that given any 2 of these 3 want and can have some kind of well-defined operations are known to the errors of that looks so I'm proposing that we have noted there's no that lots of the expense of a tricky at Lok arbitration rules and so up to uris difficult violations
of systems some research being in 2008 and something have my former employer and we often discussed how we're going to bring this so he he took it too interested in years past the way he describes here and how it's possible in practice for the binary systems have use duplicate and violations with there being no practical way to avoid them except last and current and this might be spread some so he says here about this can happen with our kernel approach that's not quite true it only applies if you don't have a lose lots of those and so it is evident that both SQL Server and Oracle as for emergent mentations exhibit this behavior in the Philips observes that don't have joined tables into joining an inline using on plays the role of the to doing the simplest possible books operation if like with merge on concurrency is getting these violations in my view and this work but I would go initially at the a couple so if you using Oracle say you want and you want to emerge that is an atomic what is apparently there is a way of doing this and it might seem familiar to other members of the audience
and so so isn't it is is in the eyes of 5 bounce back over is a month ago and so this is excellent finally concurrent uh current access to safe and answer this person is proposing that emerge in a new framework likely we use were doing insert update thing which we always thought was a crude trick turns that you have to do it with Oracle's merge in order to get those semantics which I thought was the point X so so we were we we kind of we always assume that we this this would be big necessary because merge was thought to just sort of do this for you but in fact the and with that any of the the major implementations certain that these those 2 and and this is something I don't know bit of research understood in a better understand the trade and so on and it's very evidence that you know they don't they don't they simply don't fix them with the major problems you know this is point and so he had a distance what what we call this does it doesn't do basically got near 2nd health and this is just probably the most salient example itself as you plan this is a this is the simplest possible of so there's no mystery to what what's going on here you know the knowledge and doing something using with joining in many already got duplicates on the joint calls which is undefined in by standard is a simple simple absurd and still requires this loop so you know that might be some things but it's not useful for solving basic problem of having some kind of absurd type thing in world he he mostly and so going back to the trade off
bikini any 2 alone so evidently but but uses theory like 1 to stand 18 month theory was freed from that person heard no complaints and when to be corrected if anyone better informed why am well founded the what why don't and some of the tension is the mean of the churches hence it if you
would not honestly and the others by notice that at the end the time so
going back to the trade off so the alleged cases this asteroid was nation of in-service cases where you know that this this is not acceptable and and maybe it is in theory possible to collect square the circle and get all 3 and but I am skeptical and but I think it economy done great expense and just basically isn't something that will ever reported and practice on in respect system so I mention that only 1 of the um OK In summary the implementation of all the intersection of really complicated part system and the solutions proposed are clearly chromatic tradeoffs unworkable without down and bottom think there's any alternative I've started everywhere in the worker and proposed today in Dick and that it doesn't burn through action and is and what it is semantics are are quite similar and and even with the problems of the we lost arbitration rules and doesn't matter in Texas I'm unwilling to be told that there might be cases where that isn't true but sounds it appears to be the case that that I'm aware of any complaints and about kind of thing for and this is probably the argument that appears to be the name the game here on I think that it's probably likely that every major implementation is informed by these kinds of all the arguments and in practical experience things like that and so on you know American but what is the nature of this and now that's M 1 on the slides and I
will go to question sure yet again nature
provides it wasn't always afraid
that that yet but out of what
we do is that there was a lot of money off of the of the yes this is 1 of the In the area of the world in which you want to say right well it would be that would be an unreasonable use of feature and I won't do it well it it would then well then it's not supposed to work that way and that is implicit and that you know what what is what OK other areas of the auditory well it would found it would reject a regex which was accidentally whichever 1 happened to be rejected 1st and now you might think to add control for this by actually feature but I haven't referred to before were provides possible object at the end of the reject and not be projector for that that the reject enough that reject was worse than reject doesn't have a physical and did so and by that means you could handle such complicated cases which would have be compelling for the conflict resolution type use cases I referred to earlier so if you're going to do that you're going to get a lot trouble you ought to know ahead of time where you're going to have a conflict with 1 even when you can do what I just said you can read by the time the 2 of you will have you all can use all there but anyway but but in order to know what's what what what what was wrong so it's necessary to to indicate explicitly what was intended and in particular which in a unique index was expected to be violated I would suppose found which is which is 1 which is problematic for referring to the reason that it something that Robert that did raise 1 point and I don't I don't like that very much you will be interested in the sort of the Internet of the state of what you're talking about not you know the human idiomatic usage right when I was was in the history of the use of just well it depends on the order of execution and you know even if the soul brother arbitrarily and we search in Boyd define order into unique indexes so that would be factor for 1 and so I suppose and Europe in Europe that would would be referencing something that was uh potentially collect the wrong the wrong reject are not married all other former rejected and and so this is all right but that's not within the constraints that I defined or this meant to define and that would have you only expect this in the event of 1 unique and that's where the violation is be expected to have only 2 years old with that you have what I saw this as an example of a common have 1 and only 1 unique that's on table 1st secondly it's all of its non saying that well this is a short short that the church was set in this room from 1 of the most well I suppose it 1 way would be to have an 8 predicate but you join on the neck we join but and it also is what's next but once would it yes it certainly again you certainly could get an unexpected behavior when that happened what I'd I believe that that is something that will implantations all so I I accept the at this cluster point but can only also point but you could em anticipated this case and Hamlet as well and which is not the case with some loss of the implementation yes I believe you can join there I think there are their numerous restrictions on and hello practicing the complicated predicate joining on it just doesn't work I'm not quite sure of the details and so any any practical example it almost always has been at joint and but and I don't know too much about and how word works in other systems I'm really what it was interesting that and approximation of the trade off by virtue of that down you know that that they don't handle these argument that the race condition because this is so that it's better to the state that his or the her rat all 3 affair though if if the wrong thing was projected that in fact would updates its state a home as opposed to the actual intended all of that was a long linear there is issued been wrong constraint yes that's true why are there and if the history idea the 1 thing I don't know I mean what we want to saying cracked and I'm not I'm not saying it isn't and monitoring could be a concern and while I can point to and number with implementations that have similar issues and 1 site in the text that case here character of all the all right so but it that's the that's that's true as well yes if they could get that's a separate issue here the but but that's another that's part of the deal of knowledge when we that on the 1 it's actually really 1 is actually 1 of the but also they would be that their well being going on top of 3 and a half In the 1st part of the appointment I'm being told to of the people 1 of the you could say that the only thing that the that of the value of I think if you regardless of whether or not you're you're handling at the view of potentially multiple conflicts in your handling them in line and the NL statement I can get very messy were quickly and I would suggest that was your use case you have some kind of ad hoc handling of that that yes sir many of I can send a metaphoric I really don't know and it is yes and I think it's probably something I'm going to a right on again and I did solicit feedback on it and then I actually never much about that at all I physically so severe back on of front of hackers and no and you're back to much so I don't suggest that set this approval and what's so anyone undone and analyzing questions
Automatische Indexierung
Einfügungsdämpfung
Web Site
Indexberechnung
Implementierung
Gebäude <Mathematik>
Ungerichteter Graph
Dienst <Informatik>
Gesetz <Physik>
Computeranimation
Datenhaltung
Dienst <Informatik>
Automatische Indexierung
Arbeitsplatzcomputer
Minimum
Eindeutigkeit
Implementierung
Serviceorientierte Architektur
Internetworking
Formale Semantik
Web log
Benutzerfreundlichkeit
Extrempunkt
Implementierung
Mailing-Liste
Kontextbezogenes System
Mustererkennung
Computeranimation
Formale Semantik
Richtung
Geschlecht <Mathematik>
Offene Menge
Nichtunterscheidbarkeit
Hacker
Implementierung
Metropolitan area network
Nebenbedingung
Bit
Multiplikation
Kalkül
Nebenbedingung
Gruppenkeim
Zahlenbereich
Befehl <Informatik>
Smiley
Physikalische Theorie
Computeranimation
Interrupt <Informatik>
Kugel
Datenreplikation
Visualisierung
Luenberger-Beobachter
Strom <Mathematik>
Lambda-Kalkül
Bildauflösung
Analysis
Automatische Indexierung
Verklemmung
Kategorie <Mathematik>
Eindeutigkeit
Indexberechnung
Übergang
Datenreplikation
Ereignishorizont
Fundamentalsatz der Algebra
Funktion <Mathematik>
Automatische Indexierung
Rechter Winkel
Physikalische Theorie
Zellularer Automat
Lesen <Datenverarbeitung>
Mehrrechnersystem
Eindeutigkeit
Versionsverwaltung
Arithmetisches Mittel
Manufacturing Execution System
Instantiierung
Einfügungsdämpfung
Vektorpotenzial
Subtraktion
Differenzengleichung
Ausnahmebehandlung
Soundverarbeitung
Zahlenbereich
Analytische Menge
Information
Code
Computeranimation
Formale Semantik
Netzwerktopologie
Datensatz
Multiplikation
Ungleichung
Code
Weitverkehrsnetz
Strom <Mathematik>
Transaktionsverwaltung
Tabelle <Informatik>
Lineares Funktional
Automatische Indexierung
Befehl <Informatik>
Indexberechnung
Ausnahmebehandlung
Tablet PC
Codec
Schlussregel
Einfache Genauigkeit
Netzwerktopologie
Transaktionsverwaltung
Tupel
Automatische Indexierung
Information
Eindeutigkeit
Einfügungsdämpfung
Speicherverwaltung
Tabelle <Informatik>
Punkt
Mereologie
Natürliche Zahl
Versionsverwaltung
Information
Ähnlichkeitsgeometrie
Computeranimation
Netzwerktopologie
Datenmanagement
Quick-Sort
Hacker
Automatische Indexierung
Datennetz
Schlüsselverwaltung
Übergang
p-Block
Gleichheitszeichen
Mechanismus-Design-Theorie
Invariante
Spannweite <Stochastik>
Sinusfunktion
Tupel
Datenstruktur
Automatische Indexierung
Ganze Zahl
Wurzel <Mathematik>
Konditionszahl
Gradientenverfahren
Ordnung <Mathematik>
Eindeutigkeit
Versionsverwaltung
Speicherverwaltung
Objekt <Kategorie>
Formale Semantik
Wellenlehre
Invarianz
Implementierung
Homepage
Systemprogrammierung
Pufferspeicher
Ganze Zahl
Datenstruktur
Disjunktion <Logik>
Relationale Datenbank
Protokoll <Datenverarbeitungssystem>
Eindeutigkeit
Indexberechnung
Physikalisches System
Quick-Sort
Datensatz
Objekt <Kategorie>
Netzwerktopologie
Portscanner
Bildschirmmaske
Personal Area Network
Innerer Punkt
Einfügungsdämpfung
Einfügungsdämpfung
Gewicht <Mathematik>
Natürliche Zahl
Datensichtgerät
Gruppenoperation
Computeranimation
Übergang
Homepage
Pufferspeicher
Weitverkehrsnetz
Transaktionsverwaltung
Disjunktion <Logik>
Automatische Indexierung
Nichtlinearer Operator
Kraftfahrzeugmechatroniker
Eindeutigkeit
Indexberechnung
p-Block
Datensatz
Transaktionsverwaltung
Tupel
Forcing
Automatische Indexierung
Verschlingung
Eindeutigkeit
Versionsverwaltung
Wärmeleitfähigkeit
Speicherverwaltung
Einfügungsdämpfung
Subtraktion
Web Site
Verklemmung
Gewicht <Mathematik>
Natürliche Zahl
Implementierung
Zahlenbereich
Fortsetzung <Mathematik>
Computeranimation
Formale Semantik
Systemprogrammierung
Metropolitan area network
Arbeit <Physik>
Reelle Zahl
Datentyp
Visualisierung
Luenberger-Beobachter
Schnitt <Graphentheorie>
Implementierung
Analysis
Tabelle <Informatik>
NP-hartes Problem
Prädikat <Logik>
Automatische Indexierung
Videospiel
Verklemmung
Fehlermeldung
Befehl <Informatik>
Physikalisches System
Systemaufruf
Quick-Sort
Unabhängige Menge
Datensatz
Warteschlange
Arithmetisches Mittel
Prädikat <Logik>
Bildschirmmaske
Flächeninhalt
Automatische Indexierung
Beweistheorie
Server
Verbandstheorie
p-Block
Ordnung <Mathematik>
Aggregatzustand
Fehlermeldung
Einfügungsdämpfung
Mereologie
Prozess <Physik>
Verklemmung
Web log
Datenparallelität
Regulärer Graph
Computeranimation
Homepage
Regulärer Graph
Automatische Indexierung
Schlüsselverwaltung
Kategorie <Mathematik>
Kanal <Bildverarbeitung>
Disjunktion <Logik>
Übergang
p-Block
Bitrate
Arbeitsplatzcomputer
Web log
Arithmetisches Mittel
Transaktionsverwaltung
Forcing
Automatische Indexierung
Rechter Winkel
Physikalische Theorie
Konditionszahl
Server
Dateiformat
Eindeutigkeit
Fehlermeldung
Standardabweichung
Implementierung
Schar <Mathematik>
Patch <Software>
Kombinatorische Gruppentheorie
Physikalische Theorie
Homepage
Loop
Datensatz
Informationsmodellierung
Arithmetische Folge
Hasard <Digitaltechnik>
Transaktionsverwaltung
Autorisierung
Fehlermeldung
Eindeutigkeit
Indexberechnung
Schlussregel
Binder <Informatik>
Chipkarte
Datensatz
Objekt <Kategorie>
Codec
Patch <Software>
Flächeninhalt
Loop
Dreiecksfreier Graph
Wort <Informatik>
Sichtenkonzept
Verklemmung
Versionsverwaltung
Indexberechnung
Sichtenkonzept
Computeranimation
Übergang
Formale Semantik
Teilmenge
Datensatz
Datensatz
Fuzzy-Logik
Endogene Variable
Hilfesystem
Einfügungsdämpfung
Hacker
Tabelle <Informatik>
Speicherverwaltung
Softwaretest
Einfügungsdämpfung
Sichtenkonzept
Schlüsselverwaltung
Versionsverwaltung
Zellularer Automat
Ruhmasse
Physikalisches System
Menge
Computeranimation
PROM
Arithmetischer Ausdruck
Domain-Name
Testmenge
Softwaretest
Arithmetische Folge
Offene Menge
Datenreplikation
Mereologie
Information
Versionsverwaltung
Bildgebendes Verfahren
Tabelle <Informatik>
Tabelle <Informatik>
Fehlermeldung
Schlüsselverwaltung
Befehl <Informatik>
Aggregatzustand
Computeranimation
Schlussregel
Open Source
Turtle <Informatik>
Druckverlauf
Transaktionsverwaltung
Tupel
Testmenge
Softwaretest
Transaktionsverwaltung
Retrievalsprache
Gewichtete Summe
Gruppenoperation
Zeichenvorrat
Befehl <Informatik>
Aggregatzustand
Computeranimation
Open Source
Turtle <Informatik>
Informationsmodellierung
Punkt
Softwareentwickler
Transaktionsverwaltung
Tabelle <Informatik>
Kraftfahrzeugmechatroniker
Prädikat <Logik>
Befehl <Informatik>
Fehlermeldung
Übergang
Mechanismus-Design-Theorie
Schlussregel
Tupel
Lesen <Datenverarbeitung>
Serielle Schnittstelle
Ordnung <Mathematik>
Lesen <Datenverarbeitung>
Einfügungsdämpfung
Gewichtete Summe
Implementierung
Element <Mathematik>
Extrempunkt
Computeranimation
Übergang
Metropolitan area network
Ordnung <Mathematik>
Kette <Mathematik>
Soundverarbeitung
Kraftfahrzeugmechatroniker
Automatische Indexierung
Äquivalenzklasse
Befehl <Informatik>
Sichtenkonzept
Verzweigendes Programm
Systemaufruf
Schlussregel
Computervirus
Biprodukt
Mechanismus-Design-Theorie
Systemaufruf
MIDI <Musikelektronik>
Datensatz
Portscanner
Tupel
Minimalgrad
Komplex <Algebra>
Lesen <Datenverarbeitung>
Versionsverwaltung
Lesen <Datenverarbeitung>
Tabelle <Informatik>
Nichtlinearer Operator
Server
Mathematische Logik
Sichtenkonzept
Verklemmung
Datenparallelität
Befehl <Informatik>
Schlussregel
Orakel <Informatik>
Patch <Software>
Physikalisches System
Computeranimation
Kernel <Informatik>
Datensatz
Loop
Datenparallelität
Server
Fehlermeldung
Tabelle <Informatik>
Einfügungsdämpfung
Bit
Punkt
Gewichtete Summe
Nebenbedingung
Physikalische Theorie
Framework <Informatik>
Computeranimation
Formale Semantik
Unendlichkeit
Loop
Metropolitan area network
Datentyp
Hasard <Digitaltechnik>
Punkt
Abstand
Tabelle <Informatik>
URN
Systemaufruf
Übergang
Arithmetisches Mittel
Lesen <Datenverarbeitung>
Ordnung <Mathematik>
Vollständigkeit
Eindeutigkeit
Standardabweichung
Verklemmung
Mereologie
Natürliche Zahl
Nebenbedingung
Orakel <Informatik>
Befehl <Informatik>
Extrempunkt
Computeranimation
Formale Semantik
Punkt
Ordnung <Mathematik>
Automatische Indexierung
Parametersystem
Übergang
Kontextbezogenes System
Mechanismus-Design-Theorie
Rechenschieber
Tupel
Datenparallelität
Lesen <Datenverarbeitung>
Eindeutigkeit
Versionsverwaltung
Formale Semantik
Server
Mathematische Logik
Gruppenoperation
Implementierung
Patch <Software>
Physikalische Theorie
Physikalisches System
Spieltheorie
Hasard <Digitaltechnik>
Transaktionsverwaltung
Implementierung
Tabelle <Informatik>
Division
Schlussregel
Physikalisches System
Ultraviolett-Photoelektronenspektroskopie
Schlussregel
Portscanner
Datensatz
Quadratzahl
Komplex <Algebra>
Loop
Mereologie
Parametersystem
Vollständigkeit
Innerer Punkt
Einfügungsdämpfung
Verklemmung
Punkt
Mereologie
Natürliche Zahl
Gewichtete Summe
Familie <Mathematik>
Versionsverwaltung
Ähnlichkeitsgeometrie
Computeranimation
Internetworking
Metropolitan area network
Softwaretest
Weitverkehrsnetz
Vorlesung/Konferenz
Punkt
Ordnung <Mathematik>
Hacker
Gerade
Bildauflösung
Automatische Indexierung
Parametersystem
Befehl <Informatik>
Sichtenkonzept
Approximation
Schlüsselverwaltung
Kontrolltheorie
Rohdaten
Übergang
Systemaufruf
Teilbarkeit
Ereignishorizont
Prädikat <Logik>
Tupel
Testmenge
Rechter Winkel
Automatische Indexierung
Konditionszahl
Regulärer Ausdruck <Textverarbeitung>
Ordnung <Mathematik>
Versionsverwaltung
Aggregatzustand
Tabelle <Informatik>
Nebenbedingung
Rückkopplung
Web Site
Formale Semantik
Zahlenbereich
Implementierung
Open Source
Physikalisches System
Task
Datentyp
Transaktionsverwaltung
Implementierung
Fehlermeldung
Eindeutigkeit
Physikalisches System
Quick-Sort
Schlussregel
Datensatz
Portscanner
Objekt <Kategorie>
Flächeninhalt
Mereologie
Parametersystem
Trigonometrie
Wort <Informatik>
Innerer Punkt

Metadaten

Formale Metadaten

Titel Why UPSERT is weird
Serientitel PGCon 2014
Anzahl der Teile 31
Autor Geoghegan, Peter
Mitwirkende Crunchy Data Solutions (Support)
Lizenz CC-Namensnennung 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen.
DOI 10.5446/19098
Herausgeber PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
Erscheinungsjahr 2014
Sprache Englisch
Produktionsort Ottawa, Canada

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract Counterintuitive lessons learned from the implementation effort Talk that examines implementation process on the INSERT...ON DUPLICATE KEY LOCK FOR UPDATE feature proposed for PostgreSQL. "UPSERT" can loosely be described as a DBMS feature that allows a DML statement's author to atomically either insert a row, or on the basis of the row already existing, update that existing row instead, while safely giving little to no further thought to concurrency. One of those two outcomes must be guaranteed, regardless of concurrent activity, which is the essential property of UPSERT. Examples include MySQL's INSERT...ON DUPLICATE KEY UPDATE, or the SQL standard's MERGE statement (as implemented in a number of proprietary systems). The absence of this feature from Postgres has been a long-standing complaint from Postgres users, and an effort to implement an extension to the Postgres INSERT statement, that implements something broadly in line with user expectations in this area was undertaken in 2013. This talk considers the experience of working on that implementation, and examines how the proposed INSERT...ON DUPLICATE KEY LOCK FOR UPDATE feature deals with concurrency issues, visibility issues, the interplay of value locking and row locking, and the general avoidance of unprincipled deadlocking. In order to come up with an implementation that satisfied user expectations, while still preserving and respecting long standing abstractions and conceptual precepts, some interesting and counterintuitive choices were made to resolve the fundamental tension in tying value locking (as always required on a limited scale for unique index enforcement) to row locking (to LOCK FOR UPDATE). Finally, the talk examines the strategic significance of UPSERT, and further use-cases enabled by the feature in the area of multi-master replication conflict resolution.

Ähnliche Filme

Loading...