Date: Thu, 27 Jun 2019 18:02:02 -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 ; and, \d toast show its
 main table ; and \d relkind=I show its partitions (and tablespace)
Message-ID: <20190627230201.GA9401@telsasoft.com>
References: <20190422154902.GH14223@telsasoft.com>
 <CA+FpmFfihB-okuPa0sxyhALpXcxMuHJYDS95sqvra6+D5Zkk6g@mail.gmail.com>
 <20190503142721.GC3925@telsasoft.com>
 <CA+FpmFffNcyO8CcG7O7P72K4-PuzataxT=hfWbaV=SrosMK=Cg@mail.gmail.com>
 <20190506162251.GN3925@telsasoft.com>
 <20190623142546.GE16019@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="AqsLC8rIMeq19msA"
Content-Disposition: inline
In-Reply-To: <20190623142546.GE16019@telsasoft.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--AqsLC8rIMeq19msA
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

My previous patch missed a 1-line hunk, so resending.

--AqsLC8rIMeq19msA
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v3-0000-print-table-associated-with-given-TOAST-table.patch"

From 29e4c0b9700b9dee5f6ff2abc442e08e5221eb93 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v3] 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 c65bc82..ff98c4f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2153,6 +2153,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


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

From 185d8723bec45824a0db245f69e948080b7fbbb2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v3] 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 af2f440..c65bc82 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2275,6 +2275,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


--AqsLC8rIMeq19msA
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-show-childs-and-tablespaces-of-partitioned-indice.patch"

From 7e771e08cee52e506d4df18a1316652ac7e8f516 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 19 Jun 2019 15:41:25 -0500
Subject: [PATCH v4] show childs and tablespaces of partitioned indices

---
 src/bin/psql/describe.c | 53 +++++++++++++++++++++++--------------------------
 1 file changed, 25 insertions(+), 28 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ff98c4f..5ebad24 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3063,6 +3063,7 @@ describeOneTableDetails(const char *schemaname,
 	if (tableinfo.relkind == RELKIND_RELATION ||
 		tableinfo.relkind == RELKIND_MATVIEW ||
 		tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
+		tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
 		tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
 	{
 		PGresult   *result;
@@ -3114,6 +3115,7 @@ describeOneTableDetails(const char *schemaname,
 						  " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
 						  " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'"
 						  " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE)
+						  " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_INDEX)
 						  " ORDER BY inhseqno;", oid);
 
 		result = PSQLexec(buf.data);
@@ -3178,7 +3180,8 @@ describeOneTableDetails(const char *schemaname,
 		 * Otherwise, we will not print "Partitions" section for a partitioned
 		 * table without any partitions.
 		 */
-		if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE && tuples == 0)
+		if (tuples == 0 && (tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
+					tableinfo.relkind == RELKIND_PARTITIONED_INDEX))
 		{
 			printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples);
 			printTableAddFooter(&cont, buf.data);
@@ -3188,7 +3191,7 @@ describeOneTableDetails(const char *schemaname,
 			/* print the number of child tables, if any */
 			if (tuples > 0)
 			{
-				if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
+				if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE && tableinfo.relkind != RELKIND_PARTITIONED_INDEX)
 					printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
 				else
 					printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
@@ -3198,39 +3201,33 @@ describeOneTableDetails(const char *schemaname,
 		else
 		{
 			/* display the list of child tables */
-			const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ?
+			const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE && tableinfo.relkind != RELKIND_PARTITIONED_INDEX) ?
 			_("Child tables") : _("Partitions");
 			int			ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
 
 			for (i = 0; i < tuples; i++)
 			{
-				if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
-				{
-					if (i == 0)
-						printfPQExpBuffer(&buf, "%s: %s",
-										  ct, PQgetvalue(result, i, 0));
-					else
-						printfPQExpBuffer(&buf, "%*s  %s",
-										  ctw, "", PQgetvalue(result, i, 0));
+				char *ptn_expr = tableinfo.relkind == RELKIND_PARTITIONED_TABLE ? PQgetvalue(result, i, 1) : "";
+				char	   *partitioned_note;
+				switch (*PQgetvalue(result, i, 2)) {
+				case RELKIND_PARTITIONED_INDEX:
+				case RELKIND_PARTITIONED_TABLE:
+					partitioned_note = ", PARTITIONED";
+					break;
+				default:
+					partitioned_note = "";
 				}
-				else
-				{
-					char	   *partitioned_note;
 
-					if (*PQgetvalue(result, i, 2) == RELKIND_PARTITIONED_TABLE)
-						partitioned_note = ", PARTITIONED";
-					else
-						partitioned_note = "";
-
-					if (i == 0)
-						printfPQExpBuffer(&buf, "%s: %s %s%s",
-										  ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
-										  partitioned_note);
-					else
-						printfPQExpBuffer(&buf, "%*s  %s %s%s",
-										  ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
-										  partitioned_note);
-				}
+				if (i == 0)
+					printfPQExpBuffer(&buf, "%s: %s%s%s%s",
+							ct, PQgetvalue(result, i, 0),
+							tableinfo.relkind == RELKIND_PARTITIONED_TABLE ? " " : "", ptn_expr,
+							partitioned_note);
+				else
+					printfPQExpBuffer(&buf, "%*s  %s%s%s%s",
+							ctw, "", PQgetvalue(result, i, 0),
+							tableinfo.relkind == RELKIND_PARTITIONED_TABLE ? " " : "", ptn_expr,
+							partitioned_note);
 				if (i < tuples - 1)
 					appendPQExpBufferChar(&buf, ',');
 
-- 
2.7.4


--AqsLC8rIMeq19msA--