Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint - Mailing list pgsql-hackers

On Thu, May 21, 2015 at 11:55 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> > It seems strange to force the user to think about constraint handling
>> > and
>> > then not offer them any choices once they have done the thinking.
>>
>> What if both constraints are violated? Won't the update end up in trouble?
>
>
> Great question. We don't handle that at the moment. So how do we handle
> that?

By writing two separate INSERT ... ON CONFLICT DO UPDATE statements?
There is very little or no disadvantage to doing it that way.

>> > If the update is the same no matter which constraint is violated, why
>> > would
>> > I need to specify the constraint? We're forcing the developer to make an
>> > arbitrary choice between two constraints.
>>
>> Why would the update be the same, though?
>
>
> *If* is the keyword there.

I'm having a hard time imagining a scenario in which the update would
be the same. That's why I asked how it could be. I'm asking for a
practical example involving plausible business rules.

>> How could that make sense?
>
>
> It wouldn't, that is the point. So why does the current syntax force that?
>
>>
>> You're still going to have to update both unique-indexed columns with
>> something, and that could fail.
>
>
> ISTM clear that you might want to handle each kind of violation differently,
> but we cannot.

I think you can -- with two statements.

>> > We will see many people ask why they have to specify constraints
>> > explicitly.
>>
>> I'm not sure that we will, actually, but as I said, go ahead and
>> propose removing the restriction if you think it's important (maybe
>> start a thread on it).
>
>
> I am. I have. Many times. What is wrong with this thread or all of the other
> times I said it?
>
> Please look at the $SUBJECT of this thread. We're here now.

What do you want me to do about it? I've said that I think that what
you say about not mandating the inference clause in the parser could
be okay. If you want to change it, obviously you're going to need to
get some buy in, and this thread could easily be missed. I'm not
willing to defend mandating it, and I'm not willing to argue for
removing it (to be clear, I think being able to infer a unique index
is very important, but that doesn't mean that I'm attached to
mandating it for UPDATE). That's all.

>> > As I've pointed out, if the underlying model changes then you now have
>> > to
>> > explicitly recode all the SQL as well AND time that exactly so you roll
>> > out
>> > the new code at the same time you add/change constraints. That makes it
>> > much
>> > harder to use this feature than I would like.
>>
>> If the underlying model changes, then it's good that your queries
>> break, because they're predicated on the original model. I don't think
>>
>> that happens very often at all.
>
>
> If it seldom happens, then why do we need to specify the conflict-target? If
> I know there is only one unique constraint, why can I not rely upon that
> knowledge?

You say that as if I'm giving you pushback on that point -- for the
third time, I'm not.

If there is more than one unique constraint (or if there might be in
the future), why take the chance that the update will take the wrong
path? I'm not saying that that's the overriding consideration, but it
is certainly a big consideration.

>> What is much more routine - adding
>> redundant indexes to reindex using CREATE INDEX CONCURRENTLY, or
>> changing the predicate on whatever partial unique indexes happen to be
>> defined on the table - is handled gracefully.
>
>
> What has CREATE INDEX CONCURRENTLY got to do with this? If you don't specify
> the conflict-target at all, it wouldn't matter what the indexes are. If you
> have two indexes the same then it clearly wouldn't matter which one was
> checked first.

I'm not talking about that here. What I meant is that changes to
unique indexes that don't affect the underlying model (as you put it)
don't break your queries. Changes that do *will* break your queries.
And that's definitely a good thing. I am pretty neutral on whether
it's right to mandate that DO UPDATE statements *must* buy in to this.

-- 
Peter Geoghegan