[patch] Have psql's \d+ indicate foreign partitions - Mailing list pgsql-hackers

Hi

Recently I have been working a lot with partitioned tables which contain a mix
of local and foreign partitions, and find it would be very useful to be able to
easily obtain an overview of which partitions are foreign and where they are
located.

Currently, executing "\d+" on a partitioned table lists the partitions
like this:

    postgres=# \d+ parttest
                                       Partitioned table "public.parttest"
     Column |  Type   | Collation | Nullable | Default | Storage  |
Compression | Stats target | Description
    --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
     id     | integer |           | not null |         | plain    |
         |              |
     val1   | text    |           |          |         | extended |
         |              |
     val2   | text    |           |          |         | extended |
         |              |
    Partition key: HASH (id)
    Partitions: parttest_10_0 FOR VALUES WITH (modulus 10, remainder 0),
                parttest_10_1 FOR VALUES WITH (modulus 10, remainder 1),
                parttest_10_2 FOR VALUES WITH (modulus 10, remainder 2),
                parttest_10_3 FOR VALUES WITH (modulus 10, remainder 3),
                parttest_10_4 FOR VALUES WITH (modulus 10, remainder 4),
                parttest_10_5 FOR VALUES WITH (modulus 10, remainder 5),
                parttest_10_6 FOR VALUES WITH (modulus 10, remainder 6),
                parttest_10_7 FOR VALUES WITH (modulus 10, remainder 7),
                parttest_10_8 FOR VALUES WITH (modulus 10, remainder 8),
                parttest_10_9 FOR VALUES WITH (modulus 10, remainder 9)

which doesn't help much in that respect.

Attached patch changes this output to:

    postgres=# \d+ parttest
                                       Partitioned table "public.parttest"
     Column |  Type   | Collation | Nullable | Default | Storage  |
Compression | Stats target | Description
    --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
     id     | integer |           | not null |         | plain    |
         |              |
     val1   | text    |           |          |         | extended |
         |              |
     val2   | text    |           |          |         | extended |
         |              |
    Partition key: HASH (id)
    Partitions: parttest_10_0 FOR VALUES WITH (modulus 10, remainder 0),
                parttest_10_1 FOR VALUES WITH (modulus 10, remainder
1), server: "fdw_node2",
                parttest_10_2 FOR VALUES WITH (modulus 10, remainder 2),
                parttest_10_3 FOR VALUES WITH (modulus 10, remainder
3), server: "fdw_node2",
                parttest_10_4 FOR VALUES WITH (modulus 10, remainder 4),
                parttest_10_5 FOR VALUES WITH (modulus 10, remainder
5), server: "fdw_node2",
                parttest_10_6 FOR VALUES WITH (modulus 10, remainder 6),
                parttest_10_7 FOR VALUES WITH (modulus 10, remainder
7), server: "fdw_node2",
                parttest_10_8 FOR VALUES WITH (modulus 10, remainder 8),
                parttest_10_9 FOR VALUES WITH (modulus 10, remainder
9), server: "fdw_node2"

which is much more informative, albeit a little more cluttered, but
short of using
emojis I can't see any better way (suggestions welcome).

For completeness, output with child tables could look like this:

    postgres=# \d+ inhtest
                                             Table "public.inhtest"
     Column |  Type   | Collation | Nullable | Default | Storage  |
Compression | Stats target | Description
    --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
     id     | integer |           | not null |         | plain    |
         |              |
     val1   | text    |           |          |         | extended |
         |              |
     val2   | text    |           |          |         | extended |
         |              |
    Child tables: inhtest_10_0,
                  inhtest_10_1 (server: "fdw_node2"),
                  inhtest_10_2,
                  inhtest_10_3 (server: "fdw_node2"),
                  inhtest_10_4,
                  inhtest_10_5 (server: "fdw_node2"),
                  inhtest_10_6,
                  inhtest_10_7 (server: "fdw_node2"),
                  inhtest_10_8,
                  inhtest_10_9 (server: "fdw_node2")
    Access method: heap

Will add to next CF.


Regards

Ian Barwick