seamusabshere wrote > On 7/23/14 6:03 PM, John R Pierce wrote: >> On 7/23/2014 1:45 PM, Seamus Abshere wrote: >>> What if we treat atomicity as optional? >> >> atomicity is not and never will be optional in PostgreSQL. > > I'm wondering what a minimal definition of upsert could be - possibly > separating concurrency handling out as a (rigorously defined) option for > those who need it. I don't know how you can avoid the implicit need for an "IF" in the algorithm. I guess if you had some way to force an INSERT to automatically hide any previous entries/records with the same PK you could move the checking to the read side of the equation - and deal with the necessary periodic cleanup. At this point you are basically implementing a Temporal database... If you leave the checking to occur during write why wouldn't you want an index to make that go faster? It isn't mandatory but any performant implementation is going to use one. You can enforce a "unique constraint violation" without an index so you initial premise is wrong - though again why would you want to? Also, why do you assume MongoDB doesn't use an index to execute the supplied query? From your link: "To prevent MongoDB from inserting the same document more than once, create a unique index on the name field. With a unique index, if an applications issues a group of upsert operations, exactly one update() would successfully insert a new document." Given we do not have native UPSERT I'm not sure where your question is coming from anyway. I'm not sure what the plans are for UPSERT at the moment but nothing prevents us from performing the UPSERT comparison on a non-uniqe set of columns. If the only unique index on a table is its "serial" column then you would get behavior similar to MongoDB w/o a unique index on "name". Though that does re-up the question about what happens when you issue a subsequent UPSERT and more than one matching record is returned...the most logical being apply the update to all matched records. I have difficulty imaging a situation where this would be desirable. If I am using UPSERT I am defining a complete entity that I need to cause to exist. If three of them already exist there is some differentiating factor between them that my UPSERT command would clobber. The example given in the MongoDB link is not a particularly convincing use-case. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-unique-constraint-needed-for-upsert-tp5812552p5812631.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
