From: | Ian Lawrence Barwick <barwick(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [patch] Have psql's \d+ indicate foreign partitions |
Date: | 2022-10-24 12:44:18 |
Message-ID: | CAB8KJ=j0HMdm8GxEGC36ka4zPy08zP6NGOniRSVV=mVFzwP0Qw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Attachment | Content-Type | Size |
---|---|---|
psql-slash-d-plus-show-foreign-partitions.v1.patch | text/x-patch | 10.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Nikita Malakhov | 2022-10-24 12:59:44 | Re: Pluggable toaster |
Previous Message | Ajin Cherian | 2022-10-24 12:29:34 | Re: Support logical replication of DDLs |