2022年11月6日(日) 1:39 Tom Lane <t...@sss.pgh.pa.us>:
>
> Michael Paquier <mich...@paquier.xyz> writes:
> > On Mon, Oct 24, 2022 at 09:44:18PM +0900, Ian Lawrence Barwick wrote:
> >> 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.
>
> > Hmm. I am not sure that we should add this much amount of
> > information, particularly for the server bits.
>
> FWIW, I am also in favor of adding ", FOREIGN" but no more.
> My concern is that as submitted, the patch greatly increases
> the cost of the underlying query by adding two more catalogs
> to the join. I don't think imposing such a cost on everybody
> (whether they use foreign partitions or not) is worth that. But
> we can add ", FOREIGN" for free since we have the relkind anyway.
Fair enough, make sense.
Revised version added per suggestions, which produces output 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), FOREIGN,
parttest_10_2 FOR VALUES WITH (modulus 10, remainder 2),
parttest_10_3 FOR VALUES WITH (modulus 10, remainder
3), FOREIGN,
parttest_10_4 FOR VALUES WITH (modulus 10, remainder 4),
parttest_10_5 FOR VALUES WITH (modulus 10, remainder
5), FOREIGN,
parttest_10_6 FOR VALUES WITH (modulus 10, remainder 6),
parttest_10_7 FOR VALUES WITH (modulus 10, remainder
7), FOREIGN,
parttest_10_8 FOR VALUES WITH (modulus 10, remainder 8),
parttest_10_9 FOR VALUES WITH (modulus 10, remainder 9), FOREIGN
Regards
Ian Barwick
commit 0b330a67e5941bacb815fa6dfae914c56563f7a9
Author: Ian Barwick <barw...@gmail.com>
Date: Sun Nov 6 21:08:26 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.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c645d66418..2eae519b1d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3445,6 +3445,8 @@ 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)
+ appendPQExpBufferStr(&buf, ", FOREIGN");
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..47bf56adbf 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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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, FOREIGN
-- 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, FOREIGN
\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, FOREIGN
-- 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, FOREIGN
\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, FOREIGN
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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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, FOREIGN
\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), FOREIGN
\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), FOREIGN
\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), FOREIGN
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
