Re: INSERT ... ON CONFLICT syntax issues

On 2015-05-06 13:37:07 -0700, Peter Geoghegan wrote:
> On Wed, May 6, 2015 at 1:22 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > That it supports exclusion constraints?
>
> But so does just naming the index. I don't think it's significant that
> exclusion operators are in pg_constraint -- you could just as easily
> name the index, since that's all you ultimately end up with anyway.

The index name for constraints is generated and not trivially safely
guessable for a user.

> >> I would care about the fact that you can't name a unique index with no
> >> constraint if there wasn't already a way of doing that with inference
> >> (I'm thinking in particular of partial indexes here, which never have
> >> constraints). But there is. So what's the problem?
> >
> > Personally I think a complex expression index is something many people
> > will not want to specify every time. And since partial/expression
> > indexes can't even have constraints...
>
> The downsides seem severe. A CREATE INDEX CONCURRENTLY just broke
> your statement, because you didn't account for the new, equivalent
> unique index during inference, and now you have to drop the old index
> and break application code. Is that really worth introducing just to
> prevent app devs from writing the inference specification? The
> specification explicitly documents their intent, which seems like a
> good thing.

I don't find that all that severe. It might just as well be the case
that the new unique constraint isn't intended to be handled by ON
CONFLICT. And having a inference specification that's longer than the
rest of the statement surely isn't helpful.

> Robert really disliked the idea of even naming the constraint, let
> alone the index. I'm trying to balance things, here.

I fail to see what doing something halfhearted helps.

Greetings,

Andres Freund