Subject: Re: pg_(total_)relation_size and partitioned tables
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Michael Paquier <michael.paquier@gmail.com>,
 David Rowley <david.rowley@2ndquadrant.com>
References: <495cec7e-f8d9-7e13-4807-90dbf4eec4ea@lab.ntt.co.jp>
 <CA+TgmobG69Nf4E+VKcojXZQa9FRsbr7Dd+NORJRFMh5Umer98w@mail.gmail.com>
 <CAKJS1f9Rev615s4k7JhUsqwu-3CjR0xB2F0qpZ5fvQe+mxke0w@mail.gmail.com>
 <CAB7nPqQtNv5DLD9pXMWcT2VcsJqOnRUNBKekAapYiJxV4t=tyg@mail.gmail.com>
 <CAB7nPqTKy7HRpUM0_vMjGPkM6h6pR86Aegsw+MTmzxWr3-D-tg@mail.gmail.com>
 <1a78dea4-36b7-afb7-b3f3-1b4ff319fbb4@lab.ntt.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <d5323242-b571-9317-1e5b-ee5fd96c2f4a@2ndquadrant.com>
Date: Thu, 28 Dec 2017 14:03:11 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.0
MIME-Version: 1.0
In-Reply-To: <1a78dea4-36b7-afb7-b3f3-1b4ff319fbb4@lab.ntt.co.jp>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 12/18/17 00:17, Amit Langote wrote:
> 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.

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

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services