Re: Promise index tuples for UPSERT

On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen
<anssi(dot)kaariainen(at)thl(dot)fi> wrote:
> The MySQL documentation says that "you should try to avoid using an ON
> DUPLICATE KEY UPDATE clause on tables with multiple unique indexes"[1].
> The proposed feature's documentation has the same suggestion[2]. Still,
> the feature defaults to this behavior. Why is the default something the
> documentation says you shouldn't do?

MySQL started saying that when they realized it broke their
statement-based replication. They have their own weird reasons for
disliking it. Now, that's not to minimize the risks.

The reasoning behind making the unique index specification optional is:

We cannot easily cover corner cases with another syntax - unique
indexes must be named directly to cover every case, and make the
user's intent absolutely clear. That's not obviously the case, but
consider partial unique indexes, for example. Or consider uniquely
constrained columns, with an almost equivalent uniquely constrained
expression on those same columns. On the one hand I am not comfortable
failing to support those, but on the other hand it could get very
messy to do it another way.

As we all know, naming a unique index in DML is ugly, and has poor
support in ORMs. It seems likely that we're better off making it
optional - it hasn't been much of a problem with the existing subxact
looping pattern. A lot of the time, there will only be a single unique
index anyway, or there will be a trivial serial PK unique index and
the unique index we always want to treat would-be conflicts within as
triggering the alternative UPDATE/IGNORE path.

> Going a bit further, I am wondering what is the use case of doing an
> UPSERT against multiple unique indexes? If multiple unique indexes
> UPSERT could be dropped that might allow for faster or cleaner index
> locking techniques.

I see no reason to think that. I don't think it buys us much at all.

--
Peter Geoghegan