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

On Thu, May 21, 2015 at 9:51 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> No not all, but we can evaluate the constraints one at a time in a
> consistent order.

We do so currently. Now, you point out that that might not be the most
useful ordering, and as it happens I agree. But changing that ordering
to not just be OID-ordering, but to put the PK first (and so on) isn't
going to fundamentally change anything. FWIW, I think that that much
(PK first) will usually accidentally be true anyway, because of the
way that create table statement is originally executed.

> My point is this: We do not need to explicitly specify the constraint we
> wish to test to ensure that we get deterministic behaviour. So it is
> possible to avoid specifying a constraint/conflict target and still get
> deterministic behaviour (which is essential).

It is deterministic, but omitting an inference specification still
risks taking the wrong path. You seem not be acknowledging that you
can still take the wrong path due to a dup violation in the wrong
constraint. So being guaranteed to have observed or not observed a
would-be dup violation in the PK does not buy much.

> If I have two constraints and I think about it, I would want to be able to
> specify this...
>
> INSERT
> ON CONFLICT (col1) DO UPDATE... (handle it one way)
> ON CONFLICT (col2) DO UPDATE... (handle it 2nd way)
>
> but I cannot with the current syntax.
>
> 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?

> 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? How could that make sense?
You're still going to have to update both unique-indexed columns with
something, and that could fail.

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

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

--
Peter Geoghegan