Date: Sun, 23 Jun 2019 09:25:47 -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: <20190623142546.GE16019@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>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="LpQ9ahxlCli8rRTG"
Content-Disposition: inline
In-Reply-To: <20190506162251.GN3925@telsasoft.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

I'm continuing this thread with an additional change to slash dee for
partitioned indexes.

postgres=# \d ttz_i_idx
 Partitioned index "public.ttz_i_idx"
 Column |  Type   | Key? | Definition 
--------+---------+------+------------
 i      | integer | yes  | i
btree, for table "public.ttz"
Number of partitions: 2 (Use \d+ to list them.)

postgres=# \d+ ttz_i_idx
             Partitioned index "public.ttz_i_idx"
 Column |  Type   | Key? | Definition | Storage | Stats target 
--------+---------+------+------------+---------+--------------
 i      | integer | yes  | i          | plain   | 
btree, for table "public.ttz"
Partitions: ttz1_i_idx,
            ttz2_i_idx, PARTITIONED

Showing the list of index partitions is probably not frequently useful, but
consider the case of non-default names, for example due to truncation.

I didn't update regression output; note that this patch also, by chance, causes
tablespace of partitioned indexes to be output, which I think is good and an
oversight that it isn't currently shown.

I added CF entry and including previous two patches for CFBOT purposes.

Recap: Tom, Andreas, Robert, Stephen and I agree that \d toast should show the
main table.  Rafia and Alvaro think that \d on the main table should (also?)
show its toast.

Justin

--LpQ9ahxlCli8rRTG
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


--LpQ9ahxlCli8rRTG
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


--LpQ9ahxlCli8rRTG
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v3-0002-show-childs-of-partitioned-indices.patch"

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

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

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ff98c4f..3a55dc9 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;
@@ -3178,7 +3179,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 +3190,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 +3200,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


--LpQ9ahxlCli8rRTG--