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 | j...@example.com |33 Tom | t...@example.com | 35 And the user does UPSERT values (Jack, t...@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". The proposed feature's documentation has the same suggestion. 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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers