Horton

Re: UPDATE of partition key

Amit Khandekar <amitdkhan.pg@gmail.com>

View thread View on postgr.es
From: Amit Khandekar <amitdkhan.pg@gmail.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-03-22T18:09:42Z
Lists: pgsql-hackers

Attachments

On 17 March 2017 at 16:07, Amit Khandekar <amitdkhan.pg@gmail.com> wrote:
> On 6 March 2017 at 15:11, Amit Langote <Langote_Amit_f8@lab.ntt.co.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().

Commits

  1. Avoid referencing off the end of subplan_partition_offsets.

  2. Allow UPDATE to move rows between partitions.

  3. Remove useless lookup of root partitioned rel in ExecInitModifyTable().

  4. Factor error generation out of ExecPartitionCheck.

  5. Minor preparatory refactoring for UPDATE row movement.

  6. Simplify and encapsulate tuple routing support code.

  7. Avoid coercing a whole-row variable that is already coerced.

  8. Use ResultRelInfo ** rather than ResultRelInfo * for tuple routing.

  9. Make RelationGetPartitionDispatchInfo expand depth-first.

  10. Expand partitioned tables in PartDesc order.

  11. Use a real RT index when setting up partition tuple routing.

  12. Fix transition tables for partition/inheritance.

  13. Fix confusion about number of subplans in partitioned INSERT setup.

  14. Prevent BEFORE triggers from violating partitioning constraints.

  15. Fire per-statement triggers on partitioned tables.

  16. Fix reporting of violations in ExecConstraints, again.

  17. Don't scan partitioned tables.

  18. Allow FDWs to push down quals without breaking EvalPlanQual rechecks.