Re: Promise index tuples for UPSERT

On Wed, 2014-10-08 at 01:10 -0700, Peter Geoghegan wrote:
> 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?

> 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.

The subxact approach is a bit different than the proposed UPSERT
command. It loops:

try:
INSERT INTO author VALUE('Jack', 'tom(at)example(dot)com', 34)
except UniqueConstraintViolation:
UPDATE author SET ... WHERE name = 'Jack'

while the UPSERT command does something like:

try:
INSERT INTO author VALUE('Jack', 'tom(at)example(dot)com', 34)
except UniqueConstaintViolation:
UPDATE author SET ... WHERE name = 'Jack' OR email = 'tom(at)example(dot)com' LIMIT 1;

- Anssi