Re: INSERT ... ON CONFLICT syntax issues

On Tue, May 5, 2015 at 5:27 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> To recap, there are three variants:
>
> A. INSERT ... ON CONFLICT DO NOTHING
>
> No arbiter is specified. This means that a conflict on any unique or
> exclusion constraint is not allowed (and will do nothing instead). This
> variant is only accepted for DO NOTHING.
>
> B. INSERT ... ON CONFLICT ON <constraint name> DO NOTHING/UPDATE
>
> In this variant, you explicitly specify the constraint by name.
>
> C. INSERT ... ON CONFLICT (<index params>) [WHERE <expression>] DO
> NOTHING/UPDATE
>
> This specifies an index (or indexes, in the corner case that there are
> several identical ones), by listing the columns/expressions and the
> predicate for a partial index. The list of columns and WHERE match the
> syntax for CREATE INDEX.

I would just say that there are two variants, only one of which
mandates the inference clause. But I'm nitpicking.

> That's pretty good overall. A few questions:

Thanks. I'm glad that we are now able to cover really any conceivable
use-case, while playing nice with every existing feature (now
updatable views are supported with ON CONFLICT DO UPDATE -- and we're
also going to be able to suppor subqueries in the UPDATE). We've been
incredibly thorough.

> 1. Why is the variant without specifying an index or constraint not allowed
> with DO UPDATE? I agree it might not make much sense, but then again, it
> might. If we're afraid that it's too unsafe to be the "default" if you don't
> specify any constraint, how about allowing it with a more verbose "ON
> CONFLICT ON ANY CONSTRAINT" syntax?

I think it's dangerous. It's basically wrong headed to omit any
constraint for DO UPDATE. I put a lot of effort into covering every
possible case with the inference clause, and I think it's pretty cool
that we have something that's so flexible. I don't feel bad about
forcing users to be explicit about what they want, because the only
conceivable downside is that they'll have to do a little extra typing
(if even that - it's probably going to be ORM-generated more often
than not). The upside -- not having their query break unexpectedly one
day, when a new constraint is added -- is huge.

> 2. Why can't you specify multiple constraints, even though we implicitly
> allow "any" with the first variant?

It's just an escape hatch. I don't want to encourage its over use, and
I want to keep the grammar simple.

> Finally, a couple of suggestions. It would be pretty handy to allow:
>
> INSERT ... ON CONFLICT ON PRIMARY KEY DO NOTHING/UPDATE

Is that really likely to be less verbose than naming the attributes directly?

> INSERT ... ON CONFLICT ON *CONSTRAINT* <constraint name> DO NOTHING/UPDATE
>
> That would allow the syntax can be expanded in the future to specify
> conflicts on other kind of things. The "ON PRIMARY KEY" syntax should be
> unambiguous with out, because PRIMARY is a reserved keyword, but for
> example, we might want to add "ON UNIQUE INDEX <index name>" later.

Okay, we can do that.

-- 
Peter Geoghegan