Re: ToDo: show size of partitioned table

On Wed, Nov 21, 2018 at 05:37:33PM +0100, Pavel Stehule wrote:
> st 21. 11. 2018 v 17:21 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com>
> napsal:
>> Hmm, these tests are not going to work, because they have "pavel" in the
>> expected output.
>
> I was blind, thank you for check

+create table testtable_apple(logdate date);
+create table testtable_orange(logdate date);
+create index testtable_apple_index on testtable_apple(logdate);
+create index testtable_orange_index on testtable_orange(logdate);
There are already a bunch of partition relations with multiple levels
created as part of the regression tests, so instead of creating more of
those, I would suggest to test \dP and \dPt in create_table.sql, and
\dPi in indexing.sql (please make sure to add tests for \dP with
partitioned indexes as well).

I think that you should really add the direct parent of a partition in
at least the verbose output, now for multiple partition levels things
are confusing in my opinion.  For example with such a schema:
CREATE TABLE parent_tab (id int) PARTITION BY RANGE (id);
CREATE INDEX parent_index ON parent_tab (id);
CREATE TABLE child_0_10 PARTITION OF parent_tab
  FOR VALUES FROM (0) TO (10);
CREATE TABLE child_10_20 PARTITION OF parent_tab
  FOR VALUES FROM (10) TO (20);
CREATE TABLE child_20_30 PARTITION OF parent_tab
  FOR VALUES FROM (20) TO (30);
INSERT INTO parent_tab VALUES (generate_series(0,29));
CREATE TABLE child_30_40 PARTITION OF parent_tab
FOR VALUES FROM (30) TO (40)
  PARTITION BY RANGE(id);
CREATE TABLE child_30_35 PARTITION OF child_30_40
  FOR VALUES FROM (30) TO (35);
CREATE TABLE child_35_40 PARTITION OF child_30_40
   FOR VALUES FROM (35) TO (40);
INSERT INTO parent_tab VALUES (generate_series(30,39));

Then with \dP+ I got that:
=# \dP+
            List of partitioned relations
 Schema |    Name     | Owner  |  Size  | Description
--------+-------------+--------+--------+-------------
 public | child_30_40 | ioltas | 48 kB  |
 public | parent_tab  | ioltas | 120 kB |
(2 rows)
Showing the parent partition looks like a pretty important to me as I
would expect multi-level partitions to be a frequent case (perhaps it
should show up as well in the non-verbose output?).  The field should be
NULL if the relation is the top of the tree.

Again, with the previous schema:
=# \dPi *idx
            List of partitioned indexes
 Schema |        Name        | Owner  |    Table
--------+--------------------+--------+-------------
 public | child_30_40_id_idx | ioltas | child_30_40
(1 row)
=# \dP *idx
Did not find any partitioned relations named "*idx"
I would have expected in the second case to have the partitioned
*relations* showing up in the output, and a relation can be an index as
well if the pattern matches.

Could you please address those problems first?  The basic shape of the
patch with the three new sub-commands is fine I think, so we can go
ahead with that, but the two problems reported are blockers in my
opinion.
--
Michael