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

Seamus Abshere <seamus@abshere.net> writes:
> On 7/23/14 3:40 PM, Tom Lane wrote:
>> For the OP's benefit --- the subtext John left unstated is that the
>> unique-key mechanism has already solved the problem of preventing
>> concurrent updates from creating duplicate keys.

> What if we treat atomicity as optional?

You'll get a *much* warmer response to that kind of suggestion from
MongoDB or MySQL, no doubt.  PG is not in the business of optional
data integrity.

> I just think there are a lot of non-concurrent bulk loading and
> processing workflows that could benefit from the performance advantages
> of upsert (one trip to database).

What exactly is your argument for supposing that an UPSERT without an
underlying index would perform so well?  It seems much more likely
that it'd suck, because of having to do full-table scans to look
for existing rows.

            regards, tom lane