[patch] Have psql's \d+ indicate foreign partitions
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
Attachments:
psql-slash-d-plus-show-foreign-partitions.v1.patchtext/x-patch; charset=US-ASCII; name=psql-slash-d-plus-show-foreign-partitions.v1.patchDownload
commit d5f5de96381b93a6ea1066d4abb4c6617e0af758
Author: Ian Barwick <barwick@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"
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.
Partitions: parttest_10_0 FOR VALUES WITH (modulus 10, remainder 0),
parttest_10_1 FOR VALUES WITH (modulus 10, remainder 1), server: "fdw_node2",
which is much more informative, albeit a little more cluttered, but
@@ -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)
To avoid the clutter that you mentioned, I suggest that this should show
that the table *is* foreign, but without the server - if you want to
know the server (or its options), you can run another \d command for
that (or run a SQL query).
That's similar to what's shown if the child is partitioned: a suffix
like ", PARTITIONED", but without show the partition strategy.
I had a patch to allow \d++, and maybe showing the foreign server would
be reasonable for that. But the patch got closed, evidently lack of
interest.
On 2022-Oct-24, Justin Pryzby wrote:
On Mon, Oct 24, 2022 at 09:44:18PM +0900, Ian Lawrence Barwick wrote:
+ else if (child_relkind == RELKIND_FOREIGN_TABLE && is_partitioned) + appendPQExpBuffer(&buf, ", server: \"%s\"", PQgetvalue(result, i, 4));
To avoid the clutter that you mentioned, I suggest that this should show
that the table *is* foreign, but without the server - if you want to
know the server (or its options), you can run another \d command for
that (or run a SQL query).
But 'server "%s"' is not much longer than "foreign", and it's not like
your saving any vertical space at all (you're just using space that
would otherwise be empty), so I'm not sure it is better. I would vote
for showing the server.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"You don't solve a bad join with SELECT DISTINCT" #CupsOfFail
https://twitter.com/connor_mc_d/status/1431240081726115845
2022年10月27日(木) 16:12 Alvaro Herrera <alvherre@alvh.no-ip.org>:
On 2022-Oct-24, Justin Pryzby wrote:
On Mon, Oct 24, 2022 at 09:44:18PM +0900, Ian Lawrence Barwick wrote:
+ else if (child_relkind == RELKIND_FOREIGN_TABLE && is_partitioned) + appendPQExpBuffer(&buf, ", server: \"%s\"", PQgetvalue(result, i, 4));To avoid the clutter that you mentioned, I suggest that this should show
that the table *is* foreign, but without the server - if you want to
know the server (or its options), you can run another \d command for
that (or run a SQL query).But 'server "%s"' is not much longer than "foreign", and it's not like
your saving any vertical space at all (you're just using space that
would otherwise be empty), so I'm not sure it is better. I would vote
for showing the server.
Indeed; my particular use-case is being able to see how the (foreign) tablesare
distributed over one or more foreign servers, so while being able to see whether
it's a foreign table or not helps, it's not all that much more disruptive to
include the identity of the server (unless the server's name is maxing out
NAMEDATALEN, dunno how prevalent that is in the wild, but it's not something
I've ever felt the need to do).
Regards
Ian Barwick
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.Currently, executing "\d+" on a partitioned table lists the partitions
like this:
Hmm. I am not sure that we should add this much amount of
information, particularly for the server bits. First, worth
mentioning, pg_partition_tree() is very handy when it comes to know
partition information, like:
SELECT relid, relkind
FROM pg_partition_tree('parttest') p, pg_class c
where c.oid = p.relid;
Anyway, saying that, we do something similar for partitioned indexes
and tables with \d+, aka around L3445:
if (child_relkind == RELKIND_PARTITIONED_TABLE ||
child_relkind == RELKIND_PARTITIONED_INDEX)
appendPQExpBufferStr(&buf, ", PARTITIONED");
This is the same, just for a new relkind.
--
Michael
Michael Paquier <michael@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.
regards, tom lane
2022年11月6日(日) 1:39 Tom Lane <tgl@sss.pgh.pa.us>:
Michael Paquier <michael@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
Attachments:
psql-slash-d-plus-show-foreign-partitions.v2.patchtext/x-patch; charset=US-ASCII; name=psql-slash-d-plus-show-foreign-partitions.v2.patchDownload
commit 0b330a67e5941bacb815fa6dfae914c56563f7a9
Author: Ian Barwick <barwick@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"
On Sun, Nov 06, 2022 at 09:23:01PM +0900, Ian Lawrence Barwick wrote:
Fair enough, make sense.
Fine by me and the patch looks OK. I'd like to apply this if there
are no objections.
--
Michael
Michael Paquier <michael@paquier.xyz> writes:
On Sun, Nov 06, 2022 at 09:23:01PM +0900, Ian Lawrence Barwick wrote:
Fair enough, make sense.
Fine by me and the patch looks OK. I'd like to apply this if there
are no objections.
WFM.
regards, tom lane
On Mon, Nov 07, 2022 at 01:43:22AM -0500, Tom Lane wrote:
WFM.
Okay, applied as bd95816, then.
--
Michael
2022年11月8日(火) 14:49 Michael Paquier <michael@paquier.xyz>:
On Mon, Nov 07, 2022 at 01:43:22AM -0500, Tom Lane wrote:
WFM.
Okay, applied as bd95816, then.
Thanks!
CF entry updated accordingly.
Regards
Ian Barwick