Subject: Re: [HACKERS] MERGE SQL Statement for PG11
To: Peter Geoghegan <pg@bowt.ie>, Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Andrew Dunstan <andrew.dunstan@2ndquadrant.com>,
 Pavan Deolasee <pavan.deolasee@2ndquadrant.com>,
 Stephen Frost <sfrost@snowman.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com>
 <CAH2-WzkAjSN1H-ym-sSDh+6EJWmEhyHdDStzXDB+Fxt1hcKEgg@mail.gmail.com>
 <CANP8+j+rO5jnDATve3gokRf6Jhfss5DquFxgz74rhJero8sAGQ@mail.gmail.com>
 <CA+TgmoavFdPbCeB+QUSdeQieW2rhCdR54qndO_8irHOXrCjxUg@mail.gmail.com>
 <CANP8+jLzhsmvL7fVgikCUR-ZhUCZ3JnMCGjgS49uTsCzvuNCBw@mail.gmail.com>
 <CAH2-WzmfysiLsmvmtGsd1zt25APKiHxJN20N9sDGoHYrBSUEgA@mail.gmail.com>
 <CANP8+jJYeYKjoH7TYXRvDBcG3jRWJz99hw3ubVKMUXrVko9BYQ@mail.gmail.com>
 <CAH2-WzmMpW97m_uAiVfTF1swA06KREbEkKftX6WBcYoAVi867g@mail.gmail.com>
 <CAH2-Wz=xVcWiErLo9t7og8FFL2-kKYRHY7sOxD2_bmDAaQVffQ@mail.gmail.com>
 <CANP8+jKz71SvyH0-kUvh4ZEamZ+qV0wDoRrY-4Zjpw9NVxXJyw@mail.gmail.com>
 <CAH2-Wzn_YAWLJqyByVfrHSskQP029AyZQ_fGNbV16wTC4z6f_g@mail.gmail.com>
 <CANP8+jLDsGOAx2bWL-coFZCYTXWO9NYYb5QbS-d43K4n5-AhFw@mail.gmail.com>
 <CA+TgmoZRUE5nk_Er4MpLEYAzcrRZ-BTTymg4_ayY3AUjx9U3OQ@mail.gmail.com>
 <CAH2-WznrH8d_9LwjfO1YEn-WbYJhadXS3XmG-G5BLYe=c3yqdA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <50394f44-af78-b40f-313a-5b0f8a0450e4@2ndquadrant.com>
Date: Tue, 6 Feb 2018 15:40:53 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAH2-WznrH8d_9LwjfO1YEn-WbYJhadXS3XmG-G5BLYe=c3yqdA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 02/06/2018 05:20 AM, Peter Geoghegan wrote:
> On Mon, Feb 5, 2018 at 7:56 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I don't think you get to make a unilateral decision to exclude 
>> features that work everywhere else from the scope of this patch.
>> If there is agreement that those features can be left out of scope,
>> then that is one thing, but so far all the commentary about the
>> things that you've chosen to exclude has been negative. Nor have
>> you really given any reason why they should be exempt. You've
>> pointed out that parallel query doesn't handle everything (which is
>> certainly true, but does not mean that any feature from now and the
>> end of time is allowed to exclude from scope whatever seems
>> inconvenient regardless of contrary community consensus) and you've
>> pointed out here and elsewhere that somebody could go add the
>> features you omitted later (which is also true, but misses the
>> general point that we want committed patches to be reasonably
>> complete already, not have big gaps that someone will have to fix
>> later).
> 
> For me, the concern is not really the omission of support for
> certain features as such. The concern is that those omissions hint
> that there is a problem with the design itself, particularly in the
> optimizer. Allowing subselects in the UPDATE part of a MERGE do not
> seem like they could be written as a neat adjunct to what Simon
> already came up with. If that was possible, Simon probably already
> would have done it.
> 

I agree with both of these statements wholeheartedly.

As I mentioned at the developer meeting last week, whenever someone asks
me for reasons why I find PostgreSQL better than MySQL, I usually point
them to "MySQL Restrictions and Limitations" document. Which is pretty
much a matrix of features that do not work together in some way. It
would be somewhat unfortunate to get on that route too ...

So I think the general principle should be to make the features as
complete as possible. For the more complex features that may not be
quite achievable, though, forcing us to add the pieces over multiple
releases. Which is what happened with partitioning, for example.

I think we can do that for MERGE too, assuming we actually understand

(1) why each of the pieces is missing

(2) what would it take to make it work

I'm not sure we have those answers for MERGE, though.

I haven't done anything on the MERGE patch so far, and I've been unable
to convince myself what the reasoning is for the limitations. Perhaps
there are explanations somewhere far back in the thread, but considering
how much both the implementation and our understanding of it changed
over time, I'm not sure how relevant those past arguments are.

Take for example the "sub-selects are not supported" limitation. It's
not clear to me why this does not work. I see claims that SQL standard
says something about it (but not what exactly), that it supposedly
depends on how we handle concurrency, and perhaps other reasons.
Furthermore, there are multiple places where the sub-select might be,
and I'm not sure which of those arguments applies to which case.

Without answering (2) I think it's perfectly understandable Peter is
concerned we may run into design issues later, when we try to address
some of the limitations.


I plan to go through the patch and this thread over the couple of days,
and summarize what the current status is (or my understanding of it).
That is (a) what are the missing pieces, (b) why are they missing, (c)
how we plan to address them in the future and (d) opinions on these
issues expressed by others on this thread.


kind regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services