On Fri, May 03, 2019 at 02:55:47PM +0200, Rafia Sabih wrote:
> On Mon, 22 Apr 2019 at 17:49, Justin Pryzby <[email protected]> wrote:
> >
> > It's deliberate that \dt doesn't show toast tables.
> > \d shows them, but doesn't show their indices.
> >
> > It seems to me that their indices should be shown, without having to think
> > and
> > know to query pg_index.
> >
> > postgres=# \d pg_toast.pg_toast_2600
> > TOAST table "pg_toast.pg_toast_2600"
> > Column | Type
> > ------------+---------
> > chunk_id | oid
> > chunk_seq | integer
> > chunk_data | bytea
> > Indexes:
> > "pg_toast_2600_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
>
> +1.
Thanks - what about also showing the associated non-toast table ?
postgres=# \d pg_toast.pg_toast_2620
TOAST table "pg_toast.pg_toast_2620"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
FOR TABLE: "pg_catalog.pg_trigger"
Indexes:
"pg_toast_2620_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
That could be displayed differently, perhaps in the header, but I think this is
more consistent with other display.
Justin
>From 7c15ebe408cc5f2af51120ea152e7997ee768f81 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v2 1/2] make \d pg_toast.foo show its indices
---
src/bin/psql/describe.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d7390d5..d26d986 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2274,6 +2274,7 @@ describeOneTableDetails(const char *schemaname,
else if (tableinfo.relkind == RELKIND_RELATION ||
tableinfo.relkind == RELKIND_MATVIEW ||
tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
+ tableinfo.relkind == RELKIND_TOASTVALUE ||
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
/* Footer information about a table */
--
2.7.4
>From 38f50cdb727c67ae7aece8e85caf2960e824cb65 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v2 2/2] print table associated with given TOAST table
---
src/bin/psql/describe.c | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d26d986..ebdf18a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2152,6 +2152,28 @@ describeOneTableDetails(const char *schemaname,
}
}
+ /* print table associated with given TOAST table */
+ if (tableinfo.relkind == RELKIND_TOASTVALUE)
+ {
+ PGresult *result = NULL;
+ printfPQExpBuffer(&buf,
+ "SELECT relnamespace::pg_catalog.regnamespace, relname FROM pg_class WHERE reltoastrelid = '%s'",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result) {
+ goto error_return;
+ } else if (1 != PQntuples(result)) {
+ PQclear(result);
+ goto error_return;
+ } else {
+ char *schemaname = PQgetvalue(result, 0, 0);
+ char *relname = PQgetvalue(result, 0, 1);
+ appendPQExpBuffer(&tmpbuf, _("For table: \"%s.%s\""),
+ schemaname, relname);
+ printTableAddFooter(&cont, tmpbuf.data);
+ }
+ }
+
if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
/* Get the partition key information */
--
2.7.4
