Re: Why is unique constraint needed for upsert? (treat atomicity as optional)

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.