Re: UPSERT wiki page, and SQL MERGE syntax

On Thu, Oct 16, 2014 at 11:39 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Oct 16, 2014 at 2:01 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> I want to retain CONFLICTING(), although I'm thinking about changing
>> the spelling to EXCLUDED(). While CONFLICTING() is more or less a new
>> and unprecedented style of expression, and in general that's something
>> to be skeptical of, I think it's appropriate because what we want here
>> isn't quite like any existing expression. Using an alias-like syntax
>> is misleading, since it implies that are no effects carried from the
>> firing of before row insert triggers. It's also trickier to implement
>> alias-like referencing.
>
> I think that the general consensus was against that style. I don't
> like it, and IIRC a few other people commented as well.

I think that's accurate, but I'd ask those that didn't like the style
to reconsider. Also, I am willing to use any type of special
expression, and any keyword or keywords. It doesn't have to be
function-like at all. But I believe that an alias-like syntax presents
certain difficulties.

There is the fact that this isn't a join, and so misleads users, which
I've explained. But if I have to add a range table entry for the alias
to make parse analysis of the UPDATE work in the more or less
conventional way (which is something I like a lot about the current
design), then that creates considerable headaches. I have to
discriminate against those in the optimizer, when time comes to
disallow params in the auxiliary plan. I have to think about each case
then, and I think that could add a lot more complexity than you'd
think. I'm not really sure.

Basically, it's difficult to make this work for technical reasons
precisely because what I have here isn't join-like. Can I easily
disallow "OLD.*" in a RETURNING clause (recall that we only project
inserted tuples, as always)? Even if you think that's okay, I'd rather
give an error message indicating that that makes no sense, which is
what happens right now.

Besides all that, there may also be an advantage to having something
similar to MySQL.
--
Peter Geoghegan