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

On 21 May 2015 at 14:25, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

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

Great question. We don't handle that at the moment. So how do we handle
that?

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

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

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

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

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

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