Re: INSERT ... ON CONFLICT syntax issues

On 2015-04-25 11:05:49 -0700, Peter Geoghegan wrote:
> On Sat, Apr 25, 2015 at 2:01 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > My problem with the WHERE being inside the parens in the above is that
> > it's
> > a) different from CREATE INDEX
>
> I don't think that that's an important goal.

Given that it's used to 'match' to indexes, I can't agree.

> > b) unclear whether the WHERE belongs to colb or the whole index
> > expression. The equivalent for aggregates, which I bet is going to be
> > used less often, caused a fair amount of confusing.
>
> I don't see those two situations as being comparable. The inference
> specification does not accept aggregates.

Huh? It's pretty much entirely besides the point that inference doesn't
accept aggregates. The point is that ORDER BY for aggregates has
confused users because it's inside the parens.

> > a) Why is is 'CONFLICT"? We're talking about a uniquness violation. What
> > if we, at some later point, also want to handle other kind of
> > violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ...
>
> I think that naming unique violations alone would be wrong (not to
> mention ludicrously verbose).

Why?

> The syntax has been like this for some time, and
> hasn't been a point of contention for a long time, so I thought this
> was settled.

I really don't care if it's been that for a long while. This is a not
yet commited feature.

> > b) For me there's a WITH before the index inference clause missing, to
> > have it read in 'SQL' style.
>
> I'm not seeing it. BTW, Robert was the one who initially proposed that
> the unique index inference clause follow this exact style (albeit
> before it accepted a WHERE clause to infer partial indexes, which was
> only added a couple of months ago).

So?

I guess I can live with that uglyness; but I'd like somebody else to
chime in.

> > c) Right now the UPDATE can refer to pseudo relations 'TARGET' and
> > 'EXCLUDED'. I think especially the latter doesn't fit anymore at
> > all. How about 'CONFLICTING' and 'EXISTING'? Or even NEW and OLD?
>
> NEW and OLD are terribly misleading, since surely the NEW tuple is the
> one actually appended to the relation by the UPDATE, and the OLD one
> is the existing one (not the excluded one). Plus they have all that
> intellectual baggage from rules.

What 'intellectual baggage' would that be? That they're already known to
have been used in another place? I don't see the problem.

How about EXISTING and NEW?

> """
> verb (used with object), excluded, excluding.
> 1.
> to shut or keep out; prevent the entrance of.
> 2.
> to shut out from consideration, privilege, etc.:
> Employees and their relatives were excluded from participation in the contest.
> 3.
> to expel and keep out; thrust out; eject:
> He was excluded from the club for infractions of the rules.
> """
>
> Seems pretty descriptive of the situation to me - I actually put a lot
> of thought into this. Additionally, the word is widely understood by
> non-native speakers. TARGET is also very descriptive, because it
> situationally describes either the existing tuple actually present in
> the table, or (from a RETURNING clause) the final tuple present in the
> table post-UPDATE. We use the term "target" for that pervasively (in
> the docs and in the code).

Sorry, I don't buy either argument. EXISTING and NEW would surely at
least as widely understood than EXCLUDE and TARGET. The latter does just
about no sense to me; especially from a user POV. I don't think the
existing usage of the term has much to do what it's used for here. That
it has 'morphing' characteristics imo just makes it worse, rather than
better. Besides being confusing that it has different meanings, it's far
from inconceivable that somebody wants to return values from the
preexisting, new, and merged rows.

Greetings,

Andres Freund