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

On Tue, May 19, 2015 at 2:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 19 May 2015 at 17:10, Peter Geoghegan <pg@heroku.com> wrote:
>>
>> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs <simon@2ndquadrant.com>
>> wrote:
>> > 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.
>>
>> That seems like something way worse than just allowing it for all
>> constraints.
>
>
> I'm talking about the evaluation order; it would still match all
> constraints, otherwise they wouldn't be constraints.

But it doesn't match all constraints when a would-be conflict is
detected. IOW, we lock the row and go to UPDATE, and then the user is
on their own insofar as avoiding duplicate violations goes. What might
have happened in other unique indexes (had that original would-be dup
violation not occurred) is irrelevant (with the MySQL thing, say) --
you better just get it right, and know that if a dup violation occurs
it was the one you anticipated (e.g. because there is only one unique
index anyway). With Postgres, we want to make sure that the user has
put thought into the condition they take that update path on, and so
it is mandatory (it can infer multiple unique indexes, but only when
they're basically equivalent for this purpose).

I think I agree with you, though: We should change things so that the
relcache gives indexes in something like the ordering that you
outline, rather than in the current arbitrary (though consistent) OID
order. However, I think that this should be done to avoid unnecessary
index bloat (fail early), and I don't think it makes much sense to do
it on the grounds you outline. This is because you can still easily
take the alternative path for the wrong reason, causing subtle
"logical corruption". You can still not match all indexes because one
index had a would-be dup violation (and so, as I said, it doesn't
matter what would have happened with the other ones). Maybe you still
get a dup violation from the update, "saving" you, but who wants to
rely on that?

>> > 2) Compatibility with MySQL
>>
>> But what you describe isn't compatible with MySQL. It's totally novel.
>
>
> Upthread you said
>
> "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"
>
> Similar is good and useful. Full compatibility is even better.

I actually do not feel strongly that it would be terrible to allow the
user to omit an inference clause for the DO UPDATE variant (on the
grounds of that being closer to MySQL). After all, we don't mandate
that the user specifies an explicit targetlist for INSERT, and that
seems like a footgun to me. If you want to make the case for doing
things that way, I probably will not oppose it. FWIW, I don't think
it's unreasonable to have a little discussion on fine points of
semantics like that post feature-freeze.

-- 
Peter Geoghegan