Re: UPDATE of partition key

On 17 March 2017 at 16:07, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com> wrote:
> On 6 March 2017 at 15:11, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>
>>>> But that starts to sound less attractive when one realizes that
>>>> that will occur for every row that wants to move.
>>>
>>> If we manage to call ExecSetupPartitionTupleRouting() during execution
>>> phase only once for the very first time we find the update requires
>>> row movement, then we can re-use the info.
>>
>> That might work, too. But I guess we're going with initialization in
>> ExecInitModifyTable().
>
> I am more worried about this: even the UPDATEs that do not involve row
> movement would do the expensive setup. So do it only once when we find
> that we need to move the row. Something like this :
> ExecUpdate()
> {
> ....
> if (resultRelInfo->ri_PartitionCheck &&
> !ExecPartitionCheck(resultRelInfo, slot, estate))
> {
> bool already_deleted;
>
> ExecDelete(tupleid, oldtuple, planSlot, epqstate, estate,
> &already_deleted, canSetTag);
>
> if (already_deleted)
> return NULL;
> else
> {
> /* If we haven't already built the state for INSERT
> * tuple routing, build it now */
> if (!mtstate->mt_partition_dispatch_info)
> {
> ExecSetupPartitionTupleRouting(
> mtstate->resultRelInfo->ri_RelationDesc,
> &mtstate->mt_partition_dispatch_info,
> &mtstate->mt_partitions,
> &mtstate->mt_partition_tupconv_maps,
> &mtstate->mt_partition_tuple_slot,
> &mtstate->mt_num_dispatch,
> &mtstate->mt_num_partitions);
> }
>
> return ExecInsert(mtstate, slot, planSlot, NULL,
> ONCONFLICT_NONE, estate, false);
> }
> }
> ...
> }

Attached is v2 patch which implements the above optimization. Now, for
UPDATE, ExecSetupPartitionTupleRouting() will be called only if row
movement is needed.

We have to open an extra relation for the root partition, and keep it
opened and its handle stored in
mt_partition_dispatch_info[0]->reldesc. So ExecEndModifyTable() closes
this if it is different from node->resultRelInfo->ri_RelationDesc. If
it is same as node->resultRelInfo, it should not be closed because it
gets closed as part of ExecEndPlan().