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

On Fri, Oct 24, 2014 at 10:04 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Oct 23, 2014 at 9:43 PM, Peter Geoghegan <pg@heroku.com> wrote:
>> * CONFLICTING() is renamed to EXCLUDED(). I know that some people
>> wanted me to go a different way with this. I think that there are very
>> good practical reasons not to [1], as well as good reasons related to
>> design, but I still accept that CONFLICTING() isn't very descriptive.
>> This spelling seems a lot better.
>
> Specifically, "some people" included at least three committers and at
> least one other community member no less prominent than yourself, or
> in other words, every single person who bothered to comment.  You can
> think whatever you like; the chances of it being committed that way
> are zero.

This is the situation with unique index inference all over again. You
don't like a function-like expression. Okay. It would be a lot more
helpful if instead of just criticizing, you *also* looked at my
reasons for not wanting to go with something that would necessitate
adding a dummy range table entry [1]. There are some very good
practical reasons for avoiding that. We only do that (the OLD.* and
NEW.* stuff) in the context of CREATE RULE and one or two other
things. We don't play any games like that during parse analysis of
ordinary optimizable statements, which is what this is. And those
dummy RTEs are always placeholders, AFAICT. Apart from those technical
reasons, the two situations just aren't comparable from a user-visible
perspective, but that isn't the main problem.

I don't particularly expect you to come around to my view here. But it
would be nice to have the problems with dummy RTEs and so on
acknowledged, so it's understood that that is in itself a strange new
direction for parse analysis of ordinary optimizable statements.

>> Unique index inference (i.e. the way we figure out  *which* unique
>> index to use) occurs during parse analysis. I think it would be
>> inappropriate, and certainly inconvenient to do it during planning.
>
> You're wrong.  The choice of which index to use is clearly wildly
> inappropriately placed in the parse analysis phase, and if you think
> that has any chance of ever being committed by anyone, then you are
> presuming the existence of a committer who won't mind ignoring the
> almost-immediate revert request that would draw from, at the very
> least, Tom.

Why? This has nothing to do with optimization. You either have an
appropriate unique index available, in which case you can proceed (and
the cost associated with value locking the index and so on is an
inherent cost, kind of like inserting into an index in general). Or
you don't, and you can't (you get an error, not a sequential scan).
That's what I'd call a semantic dependence on the index. Yes, that's
kind of unusual, but it's the reality.

I can have the code do unique index inference during planning instead
if you insist, but I think that that isn't useful. If you want me to
do things that way, please explain why. Otherwise, even if I simply
acquiesce to your wishes, I may end up missing the point.

> As far as syntax goes, I thought the INSERT .. ON CONFLICT UPDATE
> syntax proposed upthread was the best of any mentioned thus far.  The
> MERGE-based syntaxes proposed upthread were crazily verbose for no
> discernable benefit.

I don't think anyone is too insistent on pursuing that. I think that
the INSERT .. ON CONFLICT UPDATE syntax has more or less emerged as
the favorite.

> As much as I'd like to have this feature, your refusal to change
> anything except when asked at least three times each by about five
> different people makes this effort barely worth pursuing.  You can say
> all you like that you're receptive to feedback, but multiple people
> here are telling you that they feel otherwise.

I'm tired of your assertions about why I haven't done certain things.
It's not fair. I have incorporated a lot of feedback into V1.3 (and
previous versions), which isn't acknowledged. Also, I moved to the USA
this week, and things have been hectic. As I said in relation to
unique index inference, please consider that I haven't immediately
complied with that feedback because there are genuine technical
obstacles that at the very least make it more difficult than it
appears. And, I'm not necessarily able to comply quickly because of
time constraints.

[1] http://www.postgresql.org/message-id/CAM3SWZQhiXQi1osT14V7spjQrUpmcnRtbXJe846-EB1bC+9i1g@mail.gmail.com
-- 
Peter Geoghegan