Re: Promise index tuples for UPSERT - Mailing list pgsql-hackers

From Anssi Kääriäinen
Subject Re: Promise index tuples for UPSERT
Date
Msg-id 1412754074.8545.97.camel@TTY32
Whole thread Raw
In response to Re: Promise index tuples for UPSERT  (Simon Riggs <simon@2ndquadrant.com>)
Responses Re: Promise index tuples for UPSERT
List pgsql-hackers
On Tue, 2014-10-07 at 13:33 +0100, Simon Riggs wrote:
> Is there a way of detecting that we are updating a unique constraint
> column and then applying the HW locking only in that case? Or can we
> only apply locking when we have multiple unique constraints on a
> table?

What is the use case of doing an UPSERT into a table with multiple
unique constraints?

Consider table user with unique columns name and email and a non-unique
column age. If it has data
   Jack | jack@example.com |33    Tom | tom@example.com | 35

And the user does UPSERT values (Jack, tom@example.com, 34). The
proposed specification will pick random unique index (either name or
email index) and do an update of that row.

First, this will cause unique violation, second, doing the UPSERT on
random index seems confusing.

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?

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

1: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
2: http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html




pgsql-hackers by date:

Previous
From: Sawada Masahiko
Date:
Subject: Re: pg_receivexlog always handles -d option argument as connstr
Next
From: Michael Paquier
Date:
Subject: Re: BUG: *FF WALs under 9.2 (WAS: .ready files appearing on slaves)