Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

On Tue, May 19, 2015 at 1:36 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> On 19 May 2015 at 21:12, Peter Geoghegan <pg(at)heroku(dot)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