Re: ToDo: show size of partitioned table

Hi

čt 16. 8. 2018 v 5:52 odesílatel Mathias Brossard <postgresql(at)zoinx(dot)org>
napsal:

> The following review has been posted through the commitfest application:
> make installcheck-world: tested, failed
> Implements feature: tested, passed
> Spec compliant: not tested
> Documentation: tested, passed
>
> Hi,
>
> I'm with Melanie Plageman running the SVPUG Patch Review Meetup. I tested
> this feature on my Mac. The patch applied cleanly on master, and the
> feature worked as expected with the SQL at the bottom of this email (Jesse
> Zhang suggested the two-level partitioning). installcheck passed but
> installcheck-world did not.
>
> I do have a feedback on the implementation. The code tries to support
> older PostgreSQL server versions when declarative partitions were not
> supported before version 10 (relkind value of 'p'). Those versions will
> never return any result from the query being built. So I would suggest an
> early return from the function. The upside would be that the query building
> would be simpler. I can make patch implementing that suggestion if you want.
>

This is question - maybe we can support older partitioning based on only
inheritance - and the query can be more exact on PostgreSQL 10 and newer.

Please, send any patch. You are welcome.

Regards

Pavel

>
> Sincerely,
> -- Mathias Brossard
>
>
> CREATE TABLE partition (
> part int not null,
> value int not null
> ) PARTITION BY RANGE (part);
>
> CREATE TABLE partition_0 PARTITION OF partition FOR VALUES FROM (0) TO
> (10);
> CREATE TABLE partition_1 PARTITION OF partition FOR VALUES FROM (10) TO
> (20);
> CREATE TABLE partition_2 PARTITION OF partition FOR VALUES FROM (20) TO
> (30);
> CREATE TABLE partition_3 PARTITION OF partition FOR VALUES FROM (30) TO
> (40);
> CREATE TABLE partition_4 PARTITION OF partition FOR VALUES FROM (40) TO
> (50);
> CREATE TABLE partition_5 PARTITION OF partition FOR VALUES FROM (50) TO
> (60);
> CREATE TABLE partition_6 PARTITION OF partition FOR VALUES FROM (60) TO
> (70);
> CREATE TABLE partition_7 PARTITION OF partition FOR VALUES FROM (70) TO
> (80);
> CREATE TABLE partition_8 PARTITION OF partition FOR VALUES FROM (80) TO
> (90);
> CREATE TABLE partition_9 (
> part int not null,
> value int not null
> ) PARTITION BY RANGE (part);
>
> CREATE TABLE partition_9a PARTITION OF partition_9 FOR VALUES FROM (90) TO
> (95);
> CREATE TABLE partition_9b PARTITION OF partition_9 FOR VALUES FROM (95) TO
> (100);
> ALTER TABLE partition ATTACH PARTITION partition_9 FOR VALUES FROM (90) TO
> (100);
>
> INSERT INTO partition SELECT i % 100 AS part, i AS value FROM
> generate_series(1, 1000000) AS i;
>
> -------------------
> -- Below is the resulting output
>
> test=# \dt+
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+--------------+-------+-----------+---------+-------------
> public | partition | table | mbrossard | 0 bytes |
> public | partition_0 | table | mbrossard | 3568 kB |
> public | partition_1 | table | mbrossard | 3568 kB |
> public | partition_2 | table | mbrossard | 3568 kB |
> public | partition_3 | table | mbrossard | 3568 kB |
> public | partition_4 | table | mbrossard | 3568 kB |
> public | partition_5 | table | mbrossard | 3568 kB |
> public | partition_6 | table | mbrossard | 3568 kB |
> public | partition_7 | table | mbrossard | 3568 kB |
> public | partition_8 | table | mbrossard | 3568 kB |
> public | partition_9 | table | mbrossard | 0 bytes |
> public | partition_9a | table | mbrossard | 1800 kB |
> public | partition_9b | table | mbrossard | 1800 kB |
> (13 rows)
>
> test=# \dP+
> List of partitioned tables
> Schema | Name | Owner | Size | Description
> --------+-------------+-----------+---------+-------------
> public | partition | mbrossard | 35 MB |
> public | partition_9 | mbrossard | 3600 kB |
> (2 rows)
>
> test=# \dP+ *9
> List of partitioned tables
> Schema | Name | Owner | Size | Description
> --------+-------------+-----------+---------+-------------
> public | partition_9 | mbrossard | 3600 kB |
> (1 row)