Re: partition tree inspection functions
От | Jesper Pedersen |
---|---|
Тема | Re: partition tree inspection functions |
Дата | |
Msg-id | da9f8b6d-13bb-18fe-7777-b3be7bdd327b@redhat.com обсуждение исходный текст |
Ответ на | Re: partition tree inspection functions (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Ответы |
Re: partition tree inspection functions
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
Список | pgsql-hackers |
Hi Amit, On 07/19/2018 10:27 PM, Amit Langote wrote: > On 2018/07/19 23:18, Jesper Pedersen wrote: >> I'm thinking about how to best use these functions to generate a graph >> that represents the partition hierarchy. >> >> What about renaming pg_partition_tree_tables() to pg_partition_children(), >> and have it work like >> >> select * from pg_partition_children('p', true); >> --------- >> p >> p0 >> p1 >> p00 >> p01 >> p10 >> p11 >> (7 rows) >> >> select * from pg_partition_children('p', false); >> --------- >> p0 >> p1 >> (2 rows) >> >> e.g. if 'bool include_all' is true all nodes under the node, including >> itself, are fetched. With false only nodes directly under the node, >> excluding itself, are returned. If there are no children NULL is returned. > > That's a big change to make to what this function does, but if that's > what's useful we could make it. As an alternative, wouldn't it help to > implement the idea that Dilip mentioned upthread of providing a function > to report the level of a given table in the partition hierarchy -- 0 for > root, 1 for its partitions and so on? > Yes, Dilip's idea could work. I just don't think that pg_partition_tree_tables() as is would have a benefit over time. > Basically, as also discussed before, users can already use SQL to get the > information they want out of the relevant catalogs (pg_inherits, etc.). > But, such user queries might not be very future-proof as we might want to > change the catalog organization in the future, so we'd like to provide > users a proper interface to begin with. Keeping that in mind, it'd be > better to think carefully about what we ought to be doing here. Input > like yours is greatly helpful for that. > We could have the patch include pg_partition_root_parent and pg_partition_parent, and leave the rest for a future CommitFest such that more people could provide feedback on what they would like to see in this space. >>>> Maybe a function like pg_partition_number_of_partitions() could be of >>>> benefit to count the number of actual partitions in a tree. Especially >>>> useful in complex scenarios, >>>> >>>> select pg_partition_number_of_partitions('p') as number; >>>> >>>> number >>>> --------- >>>> 4 >>>> (1 row) >>> >>> Okay, adding one more function at this point may not be asking for too >>> much. Although, select count(*) from pg_partition_tree_tables('p') would >>> give you the count, a special function seems nice. >> >> Yeah, but I was thinking that the function would only return the number of >> actual tables that contains data, e.g. not include 'p', 'p0' and 'p1' in >> the count; otherwise you could use 'select count(*) from >> pg_partition_children('p', true)' like you said. > > Maybe call it pg_partition_tree_leaf_count() or some such then? > That could work. Best regards, Jesper
В списке pgsql-hackers по дате отправления:
Следующее
От: Robert HaasДата:
Сообщение: Re: Faster str to int conversion (was Table with large number of intcolumns, very slow COPY FROM)