Re: [HACKERS] UPDATE of partition key

On 16 February 2017 at 12:57, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/02/16 15:50, Amit Khandekar wrote:
>> On 15 February 2017 at 20:26, David Fetter <david@fetter.org> wrote:
>>> When an UPDATE can't happen, there are often ways to hint at
>>> what went wrong and how to correct it.  Violating a uniqueness
>>> constraint would be one example.
>>>
>>> When an UPDATE can't happen and the depth of the subtree is a
>>> plausible candidate for what prevents it, there might be a way to say
>>> so.
>>>
>>> Let's imagine a table called log with partitions on "stamp" log_YYYY
>>> and subpartitions, also on "stamp", log_YYYYMM.  If you do something
>>> like
>>>
>>>     UPDATE log_2017 SET "stamp"='2016-11-08 23:03:00' WHERE ...
>>>
>>> it's possible to know that it might have worked had the UPDATE taken
>>> place on log rather than on log_2017.
>>>
>>> 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.

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

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