Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Fri, Oct 24, 2014 at 4:39 PM, Petr Jelinek <petr(at)2ndquadrant(dot)com> wrote:
> If you feel so strongly that it's wrong even though everybody else seems to
> prefer it and if you at the same time feel so strongly about people changing
> minds once you implement this, maybe the best way to convince us is to show
> us the implementation (at this point it would probably have taken less of
> your time than the argument did).

No, it wouldn't have - I don't think anyone believes that. Magic
addRangeTableEntryForRelation() calls are only used in the context of
one or two utility statements that have pretty limited scope. Support
for an OLD.* style syntax would have to exist at *all* stages of query
execution, from parse analysis through to rewriting, planning, and
execution. That's the difference here - this isn't a utility command.

>> So in an UPDATE targetlist, you can assign DEFAULT to a column. Maybe
>> that's an interesting precedent. During rewriting, this gets rewritten
>> such that you end up with something that looks to the planner as if
>> the original query included a constant (this actually comes from a
>> catalog look-up for the column during rewriting). What if we spelled
>> EXCLUDING/CONFLICTING as follows:
>>
>> INSERT INTO upsert VALUES(1, 'Art') ON CONFLICT (key) UPDATE SET val =
>> EXCLUDED || 'this works' WHERE another_col != EXCLUDED;
>>
>> Then rewriting would figure these details out. From a design
>> perspective, there'd need to be a few details worked out about how
>> inference actually works - inferring *which* column the EXCLUDED
>> expression actually referred to, but it seems doable, especially given
>> the existing restrictions on the structure of the UPDATE. We're not
>> rewriting from a SetToDefault to a constant, but a SetToDefault-like
>> thing to a special Var (actually, the finished representation probably
>> makes it to the execution stage with that Var representation filled
>> in, unlike SetToDefault, but it's basically the same pattern). It
>> solves my problem with dummy range table entries. Actually, *any* new
>> kind of expression accomplishes this just as well. My concern here is
>> more around not needing cute tricks with dummy RTEs than it is around
>> being in favor of any particular expression-based syntax.
>>
>> What do you think of that?
>>
>
> Ugh, you want to auto-magically detect what value is behind the EXCLUDED
> based on how/where it's used in the UPDATE? That seems like quite a bad
> idea.

That's *exactly* how DEFAULT works within UPDATE targetlists. There
might be a few more details to work out here, but not terribly many,
and that's going to be true no matter what. 95%+ of the time, it'll
just be "val = EXCLUDED" anyway.

--
Peter Geoghegan