On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen <anssi.kaariai...@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". > 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?
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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers