Re: pg_(total_)relation_size and partitioned tables

Thanks all for your thoughts.

I agree with the Robert's point which both David and Michael seem to agree
with that we shouldn't really be changing what pg_relation_size() is doing
under the covers. And I guess the same for pg_table_size(), too. Both of
those functions and their siblings work with relations that possess
on-disk structures and have associated relations (TOAST, indexes) that in
turn possess on-disk structures. It seems quite clearly documented as
such. Partitioned tables are different in that they neither possess
on-disk structures nor have any relations (TOAST, indexes) associated
directly with them. Instead, they have partitions that are the relations
that aforementioned dbsize.c functions are familiar with.

So, I withdraw the patch I originally posted in favor of some other approach.

Reply continues below...

On 2017/12/18 11:51, Michael Paquier wrote:
> On Mon, Dec 18, 2017 at 9:29 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> The barrier here is thin. What's proposed here is already doable with
>> a WITH RECURSIVE query. So why not just documenting this query and be
>> done with it instead of complicating the code? It seems to me that the
>> performance in calling pg_relation_size() in a cascading times fashion
>> would not matter much. Or one could invent an additional cascading
>> option which scans inheritance and/or partition chains, or simply have
>> a new function.
>
> I just blogged on the matter, and here is one possibility here
> compatible with v10:
> WITH RECURSIVE partition_info
> (relid,
> relname,
> relsize,
> relispartition,
> relkind) AS (
> SELECT oid AS relid,
> relname,
> pg_relation_size(oid) AS relsize,
> relispartition,
> relkind
> FROM pg_catalog.pg_class
> WHERE relname = 'parent_name' AND
> relkind = 'p'
> UNION ALL
> SELECT
> c.oid AS relid,
> c.relname AS relname,
> pg_relation_size(c.oid) AS relsize,
> c.relispartition AS relispartition,
> c.relkind AS relkind
> FROM partition_info AS p,
> pg_catalog.pg_inherits AS i,
> pg_catalog.pg_class AS c
> WHERE p.relid = i.inhparent AND
> c.oid = i.inhrelid AND
> c.relispartition
> )
> SELECT * FROM partition_info;
>
> This is not really straight-forward. You could as well have the
> pg_relation_size call in the outer query.

Thanks Michael for coming up with that.

Do you (and/or others) think that's something that we can wrap inside a
built-in function(s), that is, one defined in system_views.sql? Or if we
decide to have new functions, say, pg_get_partitions() and/or
pg_get_partition_sizes(), we might as well implement them as C functions
inside dbsize.c. If so, do we have want to have "partition" variants of
all *_size() functions viz. pg_relation_size(), pg_total_relation_size(),
pg_indexes_size(), and pg_table_size()?

Thanks,
Amit