Re: UPDATE of partition key

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(at)fetter(dot)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.

>>> 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.

Thanks,
Amit