Re: pg_(total_)relation_size and partitioned tables

On 29 December 2017 at 08:03, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> Here is another idea. If we had a function
>
> pg_partition_root(regclass) returns regclass
>
> (returning itself for non-partitioned relations), then users can easily
> construct queries to get the results they want in different shapes, e.g.,
>
> select pg_partition_root(c.oid), c.relname, pg_table_size(c.oid)
> from pg_class c
> order by 1
>
> select pg_partition_root(c.oid), sum(pg_table_size(c.oid))
> from pg_class c
> group by 1

That seems much nicer. I assume "root" would mean the top level
partitioned table. If so, would we also want
pg_partition_parent(regclass)? Or maybe something to control the
number of "levels-up" the function would run for. If we had that then
maybe -1 could mean "go until you find a table with no parent".

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services