commit d5f5de96381b93a6ea1066d4abb4c6617e0af758 Author: Ian Barwick 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"