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.kaariainen@thl.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@example.com', 34)  except UniqueConstraintViolation:      UPDATE
authorSET ... WHERE name = 'Jack'
 

while the UPSERT command does something like:
  try:      INSERT INTO author VALUE('Jack', 'tom@example.com', 34)  except UniqueConstaintViolation:      UPDATE
authorSET ... WHERE name = 'Jack' OR email = 'tom@example.com' LIMIT 1;
 
- Anssi