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

On 19 May 2015 at 16:36, 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?
>

It would depend upon the evaluation order, but that would not break
determinism unless you allowed a random evaluation order.

Omitting the clause for DO NOTHING yet requiring it for DO UPDATE doesn't
make sense.

We should allow DO UPDATE to exclude a constraint and apply a deterministic
order to the constraints. 1. PK if it exists. 2. Replica Identity, when not
PK, 3. UNIQUE constraints in name order, like triggers, so users can define
a default evaluation order, just like they do with triggers.

> I was expecting that matching two constraints would end up UPDATEing two
> separate rows.
>

It's not clear to me how a single INSERT could cause two or more UPDATEs.

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

1) Ease of use - Unique constraints don't change very often. This saves
time for the common case where they stay the same. It also saves time if
they do change, because you avoid having to completely recode your app AND
make that happen at exactly the same time you apply the change of unique
constraint.

2) Compatibility with MySQL

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services