Re: ToDo: show size of partitioned table - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: ToDo: show size of partitioned table
Date
Msg-id CAFj8pRD_9vK08qW4biT7avR_OJHzKSEM3aK4iGfaY-w+L8xGDg@mail.gmail.com
Whole thread Raw
In response to Re: ToDo: show size of partitioned table  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Responses Re: ToDo: show size of partitioned table
List pgsql-hackers


ne 7. 4. 2019 v 20:27 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com> napsal:
On 2019-Apr-07, Pavel Stehule wrote:

> ne 7. 4. 2019 v 19:16 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com>
> napsal:
>
> > Here.
>
> + 1

BTW I'm not sure if you noticed, but I removed the error message "no
partitioned relations found" when the result was empty.  This was
mimicking \d behavior (which was justified on historical grounds), and
\drds behavior (which was justified on pattern ordering grounds); but I
see nothing that justifies it for \dP, so let's make it behave like all
the other commands and display an empty table.

And there's an additional change to make.  In the regression database,
this returns an empty table:

regression=# \dPi regress_indexing.pk5_pkey
     List of partitioned indexes
 Esquema | Nombre | Dueño | On table
---------+--------+-------+----------
(0 filas)

but the index does exist, and it is a partitioned one.  So it should be
displayed.  In fact, if I add the "n" flag, it shows:

regression=# \dPin regress_indexing.pk5_pkey
                               List of partitioned indexes
     Esquema      |  Nombre  |  Dueño   |       Parent name        |       On table       
------------------+----------+----------+--------------------------+----------------------
 regress_indexing | pk5_pkey | alvherre | regress_indexing.pk_pkey | regress_indexing.pk5
(1 fila)

I think the fact that \dPi doesn't show it is broken, and this fixes it:

@@ -3946,7 +3946,8 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
        appendPQExpBufferStr(&buf, "''");       /* dummy */
        appendPQExpBufferStr(&buf, ")\n");

-       appendPQExpBufferStr(&buf, !showNested ? " AND NOT c.relispartition\n" : "\n");
+       appendPQExpBufferStr(&buf, !showNested && !pattern ?
+                                                " AND NOT c.relispartition\n" : "");

        if (!pattern)
                appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"

In order for this to display sanely, I added the "Parent name" column if
either the "n" flag is shown or a pattern is given (previously it only
appeared in the former case).

I am thinking about it and original behave and this new behave should be expected (and unexpected too). We can go this way - I have not counter-arguments, but yes, it is more consistent with some other commands, pattern disables some other constraints.

It should be documented - Using any pattern in this case forces 'n' flag.



Note that this changes the expected output in the test; now every test
that uses a pattern displays *all* partitioned relations that match the
pattern, not just top-level ones.  I'm about +0.2 convinced that this is
desirable, but my first example above tilts the scales to changing it as
described.

I noticed this while testing after messing with the tab completion as
suggested by Justin: we already had Query_for_list_of_partitioned_tables
(which you could have used), but since \dP works for both indexes and
tables, I instead modified your new
Query_for_list_of_partitioned_relations to list both relation kinds.


has sense.

 
--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: ToDo: show size of partitioned table
Next
From: Alvaro Herrera
Date:
Subject: Re: ToDo: show size of partitioned table