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
commit d5f5de96381b93a6ea1066d4abb4c6617e0af758
Author: Ian Barwick <barw...@gmail.com>
Date: Thu Oct 20 12:45:28 2022 +0900
psql: in \d+, indicate foreign partitions
Currently with a partitioned table, \d+ lists the partitions and their
partition key, but it would be useful to see which ones, if any, are
foreign partitions.
A simple way of doing this is, for foreign partitions, to display the
name of the partition's foreign server.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c645d66418..3bb491cc6c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3371,9 +3371,12 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
" inhdetachpending,"
- " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
- "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
- "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n"
+ " s.srvname\n"
+ "FROM pg_catalog.pg_class c\n"
+ "JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid AND i.inhparent = '%s'\n"
+ "LEFT JOIN pg_catalog.pg_foreign_table ft ON (ft.ftrelid = c.oid)\n"
+ "LEFT JOIN pg_catalog.pg_foreign_server s ON (s.oid = ft.ftserver)\n"
"ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
" c.oid::pg_catalog.regclass::pg_catalog.text;",
oid);
@@ -3381,16 +3384,19 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
" false AS inhdetachpending,"
- " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
- "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
- "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n"
+ " s.srvname\n"
+ "FROM pg_catalog.pg_class c\n"
+ "JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid AND i.inhparent = '%s'\n"
+ "LEFT JOIN pg_catalog.pg_foreign_table ft ON (ft.ftrelid = c.oid)\n"
+ "LEFT JOIN pg_catalog.pg_foreign_server s ON (s.oid = ft.ftserver)\n"
"ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
" c.oid::pg_catalog.regclass::pg_catalog.text;",
oid);
else
printfPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
- " false AS inhdetachpending, NULL\n"
+ " false AS inhdetachpending, NULL, NULL\n"
"FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
"WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
"ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",
@@ -3445,6 +3451,10 @@ describeOneTableDetails(const char *schemaname,
if (child_relkind == RELKIND_PARTITIONED_TABLE ||
child_relkind == RELKIND_PARTITIONED_INDEX)
appendPQExpBufferStr(&buf, ", PARTITIONED");
+ else if (child_relkind == RELKIND_FOREIGN_TABLE && is_partitioned)
+ appendPQExpBuffer(&buf, ", server: \"%s\"", PQgetvalue(result, i, 4));
+ else if (child_relkind == RELKIND_FOREIGN_TABLE && !is_partitioned)
+ appendPQExpBuffer(&buf, " (server: \"%s\")", PQgetvalue(result, i, 4));
if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
appendPQExpBufferStr(&buf, " (DETACH PENDING)");
if (i < tuples - 1)
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 9d7610b948..1d208fa3a4 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1404,7 +1404,7 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1449,7 +1449,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1483,7 +1483,7 @@ Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
Child tables: ct3,
- ft3
+ ft3 (server: "s0")
\d+ ct3
Table "public.ct3"
@@ -1522,7 +1522,7 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1540,7 +1540,7 @@ Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
Child tables: ct3,
- ft3
+ ft3 (server: "s0")
\d+ ct3
Table "public.ct3"
@@ -1596,7 +1596,7 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
c6 | integer | | not null | | plain | |
c7 | integer | | | | plain | |
c8 | text | | | | external | |
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1614,7 +1614,7 @@ Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
Child tables: ct3,
- ft3
+ ft3 (server: "s0")
-- drop attributes recursively
ALTER TABLE fd_pt1 DROP COLUMN c4;
@@ -1629,7 +1629,7 @@ ALTER TABLE fd_pt1 DROP COLUMN c8;
c1 | integer | | not null | | plain | 10000 |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1642,7 +1642,7 @@ Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
Child tables: ct3,
- ft3
+ ft3 (server: "s0")
-- add constraints recursively
ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT;
@@ -1669,7 +1669,7 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1684,7 +1684,7 @@ Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
Child tables: ct3,
- ft3
+ ft3 (server: "s0")
DROP FOREIGN TABLE ft2; -- ERROR
ERROR: cannot drop foreign table ft2 because other objects depend on it
@@ -1716,7 +1716,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1746,7 +1746,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1773,7 +1773,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1804,7 +1804,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check;
f3 | date | | | | plain | |
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
-Child tables: ft2
+Child tables: ft2 (server: "s0")
\d+ ft2
Foreign table "public.ft2"
@@ -1862,7 +1862,7 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1)
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), server: "s0"
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
@@ -1934,7 +1934,7 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), server: "s0"
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
@@ -1962,7 +1962,7 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), server: "s0"
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
