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

On 25/10/14 00:48, Peter Geoghegan wrote:
>> You're conflating the user-visible syntax with the parse tree
>> representation in way that is utterly without foundation.  I don't
>> have a position at this point on which parse-analysis representation
>> is preferable, but it's completely wrong-headed to say that you
>> "can't" make NEW.x produce the same parse-analysis output that your
>> CONFLICTING(x) syntax would have created.  Sure, it might be harder,
>> but it's not that much harder, and it's definitely not an unsolvable
>> problem.
>
> I don't believe I did. The broader point is that the difficulty in
> making that work reflects the conceptually messiness, from
> user-visible aspects down. I can work with the difficulty, and I may
> even be able to live with the messiness, but I'm trying to bring the
> problems with it to a head sooner rather than later for good practical
> reasons. In all sincerity, my real concern is that you or the others
> will change your mind when I actually go and implement an OLD.* style
> syntax, and see the gory details. I regret it if to ask this is to ask
> too much of you, but FYI that's the thought process behind it.
>

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

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

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services