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.paquier@gmail.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