Re: INSERT ... ON CONFLICT syntax issues

On Sat, Apr 25, 2015 at 12:23 PM, Andres Freund <andres@anarazel.de> wrote:
> 95% of all users will know NEW/OLD from triggers, not rules. Where NEW
> is used in a quite comparable way.

I don't think it's comparable.

>> >> 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.
>
> Which means the term is used in a different way for INSERTs and UPDATEs
> already.

No, it isn't. It both cases the table is the one involved in the parse
analysis setTargetTable() call.

>> Maybe EXISTING is equally well understood as a word in general, but
>> it's way more ambiguous than EXCLUDED is here.
>
> What? I'm not suggesting to replace EXCLUDED by EXISTING - that'd make
> absolutely no sense. My suggesting is to have NEW refer to the tuple
> specified in the INSERT and EXISTING to the, well, pre existing tuple
> that the conflict is with.

Okay, but that doesn't change my opinion.

>> > 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.
>
> And there's been a patch (which unfortunately died because it's
> implementation wasn't good), to allow referring to the other versions of
> the tuple. It has been wished for numerous times.

Well, if that patch is ever committed, then it won't be hard to get
the behavior here too, since it is literally exactly the same code. I
don't change anything about it, and that seems to be your problem.

> the docs say:
>    Since
>    <literal>RETURNING</> is not part of the <command>UPDATE</>
>    auxiliary query, the special <literal>ON CONFLICT UPDATE</> aliases
>    (<varname>TARGET</> and <varname>EXCLUDED</>) may not be
>    referenced;  only the row as it exists after updating (or
>    inserting) is returned.
>
> So I don't understand that whole chain of argument. There's no such
> morphing behaviour, unless I miss something?

That's a documentation bug (a remnant of an earlier version). Sorry
about that. You can reference TARGET from returning. It's directly
contradicted by this much earlier statement on the INSERT doc page:

"Both aliases can be used in the auxiliary query targetlist and WHERE
clause, while the TARGET alias can be used anywhere within the entire
statement (e.g., within the RETURNING clause)"

I'll go fix that.

> 2a5d80b27d2c5832ad26dde4651c64dd2004f401:
>> The problem with this seems to be that it more or less
>> necessitates making both IGNORE and UPDATE fully reserved keywords in
>> order to avoid an ambiguity, which we prefer not to do
>
> It does not. As mentioned in the thread DO UPDATE/NOTHING work without
> anything like that.

I just mean that it couldn't work as-was in the repo at that time.
This commit message was written before your proposal of 8 hours ago.

We're going to have to agree to disagree here. I've given you my
opinion. I'm burnt out on this patch, and whatever the path of least
resistance is is the path I'll take. Frankly, the only reason that I'm
putting up any kind of argument is because I don't think that your
proposal is the path of least resistance.

-- 
Peter Geoghegan