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

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

95% of all users will know NEW/OLD from triggers, not rules. Where NEW
is used in a quite comparable way.

> >> 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.  To me it sounds like it's a remnant of your earlier syntax
proposal for UPSERT.

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

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


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

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

the docs say:  Since  <literal>RETURNING</> is not part of the <command>UPDATE</>  auxiliary query, the special
<literal>ONCONFLICT UPDATE</> aliases  (<varname>TARGET</> and <varname>EXCLUDED</>) may not be  referenced;  only the
rowas 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?

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.

Greetings,

Andres Freund