Re: ToDo: show size of partitioned table

út 18. 12. 2018 v 8:49 odesílatel Amit Langote <
Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> napsal:

> Hi,
>
> Thank you for updating the patch.
>
> On 2018/12/17 17:48, Pavel Stehule wrote:
> > new update of this patch
>
> Documentation portion of this patch still contains some typos that I
> mentioned before here:
>
>
> https://www.postgresql.org/message-id/1c83bb5c-47cd-d796-226c-e95795b05551%40lab.ntt.co.jp
>
> + .. If the form <literal>\dP+</literal>
> + is used, the sum of size of related partitions (including the
> + table and indexes, if any) and a description
> + are also displayed.
>
> + ... If the form <literal>\dPi+</literal>
> + is used, the sum of size of related indexes and a description
> + are also displayed.
>
> + ... If the form <literal>\dPt+</literal>
> + is used, the sum of size of related tables and a description
> + are also displayed.
>
> In all of the three hunks:
>
> the sum of size of -> the sum of "sizes" of
>
> and a description -> and associated description
>

fixed

>
> > changes:
> >
> > 1. only root partitioned tables are displayed - you can see quickly total
> > allocated space. It is not duplicated due nested partitions.
>
> +1
>
> If one wants to see a non-root partitioned table's details, they can use
> \dP+ <pattern>.
>
> > I can imagine new additional flag - line "n" nested - and then we can
> > display nested partitioned tables with parent table info. Some like
> >
> > \dPt - show only root partition tables
> > \dPnt or \dPtn - show root and nested partitioned tables
>
> Too much complication maybe?
>

I wrote it - the setup query is more complex, but not too much. I fixed the
size calculation, when nested partitions tables are visible - it calculate
partitions only from level1 group. Then the displayed size is same as total
size

postgres=# \dP+
List of partitioned relations
┌────────┬────────────┬───────┬────────┬─────────────┐
│ Schema │ Name │ Owner │ Size │ Description │
╞════════╪════════════╪═══════╪════════╪═════════════╡
│ public │ parent_tab │ pavel │ 120 kB │ │
└────────┴────────────┴───────┴────────┴─────────────┘
(1 row)

postgres=# \dPn+
List of partitioned relations
┌────────┬─────────────┬───────┬─────────────┬───────┬─────────────┐
│ Schema │ Name │ Owner │ Parent name │ Size │ Description │
╞════════╪═════════════╪═══════╪═════════════╪═══════╪═════════════╡
│ public │ child_30_40 │ pavel │ parent_tab │ 48 kB │ │
│ public │ parent_tab │ pavel │ │ 72 kB │ │
└────────┴─────────────┴───────┴─────────────┴───────┴─────────────┘
(2 rows)

postgres=# \dPn+ *
List of partitioned relations or indexes
┌────────┬────────────────────┬───────┬───────────────────┬──────────────┬─────────────┬───────┬─────────────┐
│ Schema │ Name │ Owner │ Type │ Parent name │
On table │ Size │ Description │
╞════════╪════════════════════╪═══════╪═══════════════════╪══════════════╪═════════════╪═══════╪═════════════╡
│ public │ child_30_40 │ pavel │ partitioned table │ parent_tab
│ │ 16 kB │ │
│ public │ child_30_40_id_idx │ pavel │ partitioned index │ parent_index │
child_30_40 │ 32 kB │ │
│ public │ parent_index │ pavel │ partitioned index │ │
parent_tab │ 48 kB │ │
│ public │ parent_tab │ pavel │ partitioned table │
│ │ 24 kB │ │
└────────┴────────────────────┴───────┴───────────────────┴──────────────┴─────────────┴───────┴─────────────┘
(4 rows)

> > 2. \dP without pattern shows root partitioned tables + total relation
> size.
> > When pattern is defined, then shows tables and indexes + table size
> >
> > postgres=# \dP+
> > List of partitioned relations
> > ┌────────┬────────────┬───────┬────────┬─────────────┐
> > │ Schema │ Name │ Owner │ Size │ Description │
> > ╞════════╪════════════╪═══════╪════════╪═════════════╡
> > │ public │ parent_tab │ pavel │ 120 kB │ │
> > └────────┴────────────┴───────┴────────┴─────────────┘
> > (1 row)
> >
> > postgres=# \dP+ *
> > List of partitioned relations or indexes
> >
> ┌────────┬──────────────┬───────┬───────────────────┬────────────┬───────┬─────────────┐
> > │ Schema │ Name │ Owner │ Type │ Table │ Size
> │
> > Description │
> >
> ╞════════╪══════════════╪═══════╪═══════════════════╪════════════╪═══════╪═════════════╡
> > │ public │ parent_index │ pavel │ partitioned index │ parent_tab │ 80 kB
> > │ │
> > │ public │ parent_tab │ pavel │ partitioned table │ │ 40 kB
> > │ │
> >
> └────────┴──────────────┴───────┴───────────────────┴────────────┴───────┴─────────────┘
> > (2 rows)
> >
> > postgres=# \dP+ *index
> > List of partitioned relations or indexes
> >
> ┌────────┬──────────────┬───────┬───────────────────┬────────────┬───────┬─────────────┐
> > │ Schema │ Name │ Owner │ Type │ Table │ Size
> │
> > Description │
> >
> ╞════════╪══════════════╪═══════╪═══════════════════╪════════════╪═══════╪═════════════╡
> > │ public │ parent_index │ pavel │ partitioned index │ parent_tab │ 80 kB
> > │ │
> >
> └────────┴──────────────┴───────┴───────────────────┴────────────┴───────┴─────────────┘
> > (1 row)
>
> Looking at the patch:
>
> + if (pattern)
> + /* translator: objects_name is "indexes", "tables"
> or "relations" */
> + psql_error("Did not find any partitioned %s named
> \"%s\".\n",
> + objects_name,
> + pattern);
> + else
> + /* translator: object_name is "index", "table" or
> "relation" */
> + psql_error("Did not find any partitioned %s.\n",
> + object_name);
>
> It seems that objects_name and object_name need to be swapped between the
> if and else blocks, and so do /* translator: ... */ comments.
>
> if (pattern)
> /* translator: object_name is "index", "table" or "relation" */
> psql_error(..., object_name);
> else
> /* translator: objects_name is "indexes", "tables" or "relations" */
> psql_error(..., objects_name);
>
> That is, it should say, "Did not find any partitioned index/table/relation
> named "foo" and "Did not find any partitioned indexes/tables/relations".
>

fixed

I am sending updated patch

Regards

Pavel

>
> Thanks,
> Amit
>
>