Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers

On Mon, Mar 12, 2018 at 5:43 PM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:

On Sun, Mar 11, 2018 at 11:18 AM, Peter Geoghegan <pg@bowt.ie> wrote:

As you know, there is an ON CONFLICT DO UPDATE + partitioning patch in
the works from Alvaro. In your explanation about that approach that
you cited, you wondered what the trouble might have been with ON
CONFLICT + partitioning, and supposed that the issues were similar
there. Are they? Has that turned up much?

Well, I initially thought that ON CONFLICT DO UPDATE on partition table may have the same challenges, but that's probably not the case. For INSERT ON CONFLICT it's still just an INSERT path, with some special handling for UPDATEs. Currently, for partition or inherited table, UPDATEs/DELETEs go via inheritance_planner() thus expanding inheritance for the result relation where as INSERTs go via simple grouping_planner(). 

For MERGE, we do all three DMLs. That doesn't mean we could not re-implement MERGE on the lines of INSERTs, but that would most likely mean complete re-writing of the UPDATEs/DELETEs for partition/inheritance tables. The challenges would just be the same in both cases.

Having thought more about this in the last couple of days, I am actually inclined to try out rewrite the UPDATE handling of MERGE on the lines of what ON CONFLICT DO UPDATE patch is doing. This might help us to completely eliminate invoking inheritance_planner() for partition table and that will be a huge win for tables with several hundred partitions. The code might also look much cleaner that way. I am gonna give it a try for next couple of days and see if its doable.