On Tue, May 19, 2015 at 1:36 PM, Geoff Winkless <pgsqlad...@geoff.dj> wrote: > On 19 May 2015 at 21:12, Peter Geoghegan <p...@heroku.com> wrote: >> >> It's trivial to modify Postgres to not require that a specific unique >> index be inferred, so that you can omit the inference specification >> for DO UPDATE just as you can for DO NOTHING. That would make it work >> in a similar way to MySQL; whatever actually conflict was detected >> would be assumed to be cause to take the alternative update path. > > > Except that would break the deterministic behaviour, surely? Because if you > only updated one row based on which constraint matched first, the row that > was updated would depend on the order in which the constraints were > evaluated, yes? I was expecting that matching two constraints would end up > UPDATEing two separate rows.
Well, it would be deterministic to the extent that the indexes would be evaluated in OID order. But yes, the first would-be duplicate violation would make the update path be taken once and only once for the row proposed for insertion -- at that point, you've given up on insertion (unless there is a row locking conflict). Just like MySQL, I believe. How can you find a would-be violation without inserting? How can you insert without also violating the other thing? It's far messier than it first appears. >> I have a hard time imagining why you'd ever not want to be explicit >> about what to take the alternative path on for the DO UPDATE variant. >> >> What do you have in mind? > > > If I'm being honest, my main driver is laziness :) I don't mind specifying > the constraint if I can understand why it's required, but otherwise it just > seems like I need to do more typing for no reason. Especially when there's > only one unique constraint on a table. Well, I don't have zero sympathy for that, but I'm pretty sure that that's what other people wanted. If I'm being honest, I don't actually remember how true that was. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
