Re: [HACKERS] UPDATE of partition key

On 16 February 2017 at 14:42, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/02/16 17:55, Amit Khandekar wrote:
>> On 16 February 2017 at 12:57, Amit Langote wrote:
>>> On 2017/02/16 15:50, Amit Khandekar wrote:
>>>> On 15 February 2017 at 20:26, David Fetter <david@fetter.org> wrote:
>>>>> Does that make sense, and if so, is it super invasive to HINT that?
>>>>
>>>> Yeah, I think it should be possible to find the root partition with
>>>
>>> I assume you mean root *partitioned* table.
>>>
>>>> the help of pg_partitioned_table,
>>>
>>> The pg_partitioned_table catalog does not store parent-child
>>> relationships, just information about the partition key of a table.  To
>>> get the root partitioned table, you might want to create a recursive
>>> version of get_partition_parent(), maybe called
>>> get_partition_root_parent().  By the way, get_partition_parent() scans
>>> pg_inherits to find the inheritance parent.
>>
>> Yeah. But we also want to make sure that it's a part of declarative
>> partition tree, and not just an inheritance tree ? I am not sure
>> whether it is currently possible to have a mix of these two. May be it
>> is easy to prevent that from happening.
>
> It is not possible to mix declarative partitioning and regular
> inheritance.  So, you cannot have a table in a declarative partitioning
> tree that is not a (sub-) partition of the root table.

Ok, then that makes things easy.

>
>>>> and then run ExecFindPartition()
>>>> again using the root. Will check. I am not sure right now how involved
>>>> that would turn out to be, but I think that logic would not change the
>>>> existing code, so in that sense it is not invasive.
>>>
>>> I couldn't understand why run ExecFindPartition() again on the root
>>> partitioned table, can you clarify?  ISTM, we just want to tell the user
>>> in the HINT that trying the same update query with root partitioned table
>>> might work. I'm not sure if it would work instead to find some
>>> intermediate partitioned table (that is, between the root and the one that
>>> update query was tried with) to include in the HINT.
>>
>> What I had in mind was : Give that hint only if there *was* a
>> subpartition that could accommodate that row. And if found, we can
>> only include the subpartition name.
>
> Asking to try the update query with the root table sounds like a good
> enough hint.  Trying to find the exact sub-partition (I assume you mean to
> imply sub-tree here) seems like an overkill, IMHO.
Yeah ... I was thinking , anyways it's an error condition, so why not
let the server spend a bit more CPU and get the right sub-partition
for the message. If we decide to write code to find the root
partition, then it's just a matter of another function
ExecFindPartition().

Also, I was thinking : give the hint *only* if we know there is a
right sub-partition. Otherwise, it might distract the user.

>
> Thanks,
> Amit
>
>



-- 
Thanks,
-Amit Khandekar
EnterpriseDB Corporation
The Postgres Database Company