Re: [HACKERS] MERGE SQL Statement for PG11

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

>
>
> On Sun, Mar 11, 2018 at 11:18 AM, Peter Geoghegan <pg(at)bowt(dot)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.

Thanks,
Pavan