Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Feb 6, 2018 at 8:10 PM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

>
>
> 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.
>
>
Thanks Tomas. That will certainly help a lot.

Here is v15 of the patch. It now fully supports partitioned tables. As I
explained upthread, supporting partitioned table turned out much trickier
than what I initially thought because of complete different code paths that
INSERT and UPDATE/DELETE take in case of inheritance. Since MERGE need both
the facilities, I'd to pretty much merge both the machineries. But the end
result seems okay. I am sure we can improve this further, but whatever I
have tested so far (which may not be necessarily thorough) seems to work
fine.

Since the way RIGHT OUTER join is now pushed below the ModifyTable node,
I'd to make appropriate changes to EvalPlanQual calling locations so that
we pass in the RT index of the table used in the join, and not of the
resultRelInfo, when we are handling MERGE.

Initially I was a bit surprised that EvalPlanQual silently ignores the case
when partition key is updated and a row is moved from one partition to
another. But then I realised that this is the behaviour of the partitioned
tables and not MERGE itself.

The revised version also supports subqueries in SET targetlist as well as
WHEN AND conditions. As expected, this needed a minor tweak in
query/expression mutators. So once I worked on that for partitioned tables,
subqueries started working with very minimal adjustments elsewhere. Other
things such as whole-var references are still broken. A few new tests are
also added.

Next I am going to look at RLS. While I've no prior experience with RLS, I
am expecting it to be less cumbersome in comparison to partitioning. I am
out of action till Monday and may not be able to respond in time. But any
reviews and comments are appreciated as always.

Thanks,
Pavan

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