Date: Fri, 3 May 2019 09:27:22 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Rafia Sabih <rafia.pghackers@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: make \d pg_toast.foo show its indices
Message-ID: <20190503142721.GC3925@telsasoft.com>
References: <20190422154902.GH14223@telsasoft.com>
 <CA+FpmFfihB-okuPa0sxyhALpXcxMuHJYDS95sqvra6+D5Zkk6g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="brEuL7wsLY8+TuWz"
Content-Disposition: inline
In-Reply-To: 
 <CA+FpmFfihB-okuPa0sxyhALpXcxMuHJYDS95sqvra6+D5Zkk6g@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--brEuL7wsLY8+TuWz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, May 03, 2019 at 02:55:47PM +0200, Rafia Sabih wrote:
> On Mon, 22 Apr 2019 at 17:49, Justin Pryzby <pryzby@telsasoft.com> 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

--brEuL7wsLY8+TuWz
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v2-0001-make-d-pg_toast.foo-show-its-indices.patch"

From 7c15ebe408cc5f2af51120ea152e7997ee768f81 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
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


--brEuL7wsLY8+TuWz
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v2-0002-print-table-associated-with-given-TOAST-table.patch"

From 38f50cdb727c67ae7aece8e85caf2960e824cb65 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
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


--brEuL7wsLY8+TuWz--