Re: INSERT ... ON CONFLICT syntax issues - Mailing list pgsql-hackers

On Sat, Apr 25, 2015 at 11:24 AM, Andres Freund <andres@anarazel.de> wrote:
>> > 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.

Would any alternative cause less confusion? That's the real issue. And
I'm unconvinced that your alternative would.

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

Because, as I said, it might not be a unique violation at all. It
could be an exclusion violation.

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

So, his opinion matters if it comes down to a vote. The inference
specification syntax as implemented is exactly what he suggested (plus
I've added a predicate).

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

Agreed.

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

The problem is that they make you think of rules, and they don't
describe what's going on at all.

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

Yes it does. The UPDATE docs refer to the target table in a way
intended to distinguish it from any joined-to table (FROM table). It's
clear as day. Maybe EXISTING is equally well understood as a word in
general, but it's way more ambiguous than EXCLUDED is 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.

This is how RETURNING works from UPDATEs in general. IOW, if you do an
UPDATE FROM (which is pretty similar to ON CONFLICT UPDATE,
syntax-wise), then you can only refer to the joined table's tuple and
the final post-update tuple from within RETURNING. You cannot refer to
the pre-UPDATE target tuple there either -- it's *exactly* the same
situation. Why should it be any different here? The
situational/morphing characteristic of the alias name TARGET is
therefore absolutely appropriate, in that it follows UPDATE.

To be fair, there is one unrelated slight difference with RETURNING
and conventional UPDATEs: You cannot return the EXCLUDED tuple (in the
same way that you can reference the joined-FROM tuple within
conventional UPDATEs). This is because the pertinent information is
likely to be in the target tuple (after all, the DML statement names
the proposed-for-insertion tuples itself, directly), but more
importantly because projecting both would necessitate *always*
qualifying the RETURNING column names to resolve which tuple is
intended (UPDATE FROM will seldom be a self-join, but this will always
be like a self-join).

-- 
Peter Geoghegan