On 16 February 2017 at 12:57, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/02/16 15:50, Amit Khandekar wrote: >> On 15 February 2017 at 20:26, David Fetter <da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
