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.
