Date: Fri, 18 Dec 2020 00:58:08 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-hackers@postgresql.org
Subject: Re: allow to \dtS+ pg_toast.*
Message-ID: <20201218065808.GY30237@telsasoft.com>
References: <20201130165436.GX24052@telsasoft.com>
 <1839fe7148f3aa1a7b28da9064a5c2f8b08316c3.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="kR3zbvD4cgoYnS/6"
Content-Disposition: inline
In-Reply-To: <1839fe7148f3aa1a7b28da9064a5c2f8b08316c3.camel@cybertec.at>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


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

Thanks for looking

On Thu, Dec 17, 2020 at 04:16:52PM +0100, Laurenz Albe wrote:
> On Mon, 2020-11-30 at 10:54 -0600, Justin Pryzby wrote:
> > This makes toast tables a bit less special and easier to inspect.
> > 
> > postgres=# \dtS+ pg_toast.pg_toast_2619
> >  pg_toast | pg_toast_2619 | toast table | pryzbyj | permanent   | heap          | 56 kB | 
> > 
> > This follows commit from last year:
> > | eb5472da9 make \d pg_toast.foo show its indices ; and, \d toast show its main table
> 
> This would indeed be convenient.
> 
> While playing around with it, I found the following oddity:
> 
> regression=# \dtS pg_toast.pg_toast_30701
>  pg_toast | pg_toast_30701 | toast table | laurenz
> 
> regression=# \dt pg_toast.pg_toast_30701
> Did not find any relation named "pg_toast.pg_toast_30701".
> 
> Now this doesn't seem right.  To my understanding, \dtS should do the same as \dt,
> except that it should also search in "pg_catalog" if no schema was provided.

You mean that if pg_toast.* should be shown if a matching "pattern" is given,
even if "S" was not used.  I think you're right.  The behavior I implemented
was intended to provide a bit of historic compatibility towards hiding toast
tables, but I think it's not needed, since they're not shown anyway unless
someone includes "S", specifies the "pg_toast." schema, or pg_toast is in their
search path.  See attached.

> Another thing that is missing is tab completion for
> regression=# \dtS pg_toast.pg_
> This should work just like for \d and \dS.

Tom objected to this in the past, humorously to me:

https://www.postgresql.org/message-id/14255.1536781029@sss.pgh.pa.us
On Wed, Sep 12, 2018 at 03:37:09PM -0400, Tom Lane wrote:
> Arthur Zakirov <a.zakirov@postgrespro.ru> writes:
> > On Sun, Jul 29, 2018 at 07:42:43PM -0500, Justin Pryzby wrote:
> >>> Actually..another thought: since toast tables may be VACUUM-ed, should I
> >>> introduce Query_for_list_of_tpmt ?
> 
> >> I didn't include this one yet though.
> 
> > I think it could be done by a separate patch.
> 
> I don't actually think that's a good idea.  It's more likely to clutter
> peoples' completion lists than offer anything they want.  Even if someone
> actually does want to vacuum a toast table, they are not likely to be
> entering its name via tab completion; they're going to have identified
> which table they want via some query, and then they'll be doing something
> like copy-and-paste out of a query result.

--kR3zbvD4cgoYnS/6
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0001-Allow-to-dti-pg_toast.patch"

From e56c26f763ca0caab4fb48078e297bdd6ba04276 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 28 Oct 2020 23:51:39 -0500
Subject: [PATCH] Allow to \dti+ pg_toast.*

This reverts commit 81fc5df83.  Rather than being consistent by hiding toast
tables, instead allow toast tables to be displayed by specifying "S", or their
schema, or by including pg_toast in search_path.
---
 src/bin/psql/describe.c | 24 ++++++------------------
 1 file changed, 6 insertions(+), 18 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8923eee752..b7a8bd4121 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3762,6 +3762,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 					  " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
 					  " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
 					  " WHEN 's' THEN '%s'"
+					  " WHEN " CppAsString2(RELKIND_TOASTVALUE) " THEN '%s'"
 					  " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
 					  " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
 					  " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
@@ -3775,6 +3776,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 					  gettext_noop("index"),
 					  gettext_noop("sequence"),
 					  gettext_noop("special"),
+					  gettext_noop("toast table"),
 					  gettext_noop("foreign table"),
 					  gettext_noop("partitioned table"),
 					  gettext_noop("partitioned index"),
@@ -3870,6 +3872,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ",");
 	if (showSystem || pattern)
 		appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
+	if ((showSystem || pattern) && showTables)
+		appendPQExpBufferStr(&buf, "'t',"); /* toast tables */
 	if (showForeign)
 		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
 
@@ -3878,17 +3882,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 
 	if (!showSystem && !pattern)
 		appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
+							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
-	/*
-	 * TOAST objects are suppressed unconditionally.  Since we don't provide
-	 * any way to select RELKIND_TOASTVALUE above, we would never show toast
-	 * tables in any case; it seems a bit confusing to allow their indexes to
-	 * be shown.  Use plain \d if you really need to look at a TOAST
-	 * table/index.
-	 */
-	appendPQExpBufferStr(&buf, "      AND n.nspname !~ '^pg_toast'\n");
-
 	processSQLNamePattern(pset.db, &buf, pattern, true, false,
 						  "n.nspname", "c.relname", NULL,
 						  "pg_catalog.pg_table_is_visible(c.oid)");
@@ -4101,17 +4097,9 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
 
 	if (!pattern)
 		appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
+							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
-	/*
-	 * TOAST objects are suppressed unconditionally.  Since we don't provide
-	 * any way to select RELKIND_TOASTVALUE above, we would never show toast
-	 * tables in any case; it seems a bit confusing to allow their indexes to
-	 * be shown.  Use plain \d if you really need to look at a TOAST
-	 * table/index.
-	 */
-	appendPQExpBufferStr(&buf, "      AND n.nspname !~ '^pg_toast'\n");
-
 	processSQLNamePattern(pset.db, &buf, pattern, true, false,
 						  "n.nspname", "c.relname", NULL,
 						  "pg_catalog.pg_table_is_visible(c.oid)");
-- 
2.17.0


--kR3zbvD4cgoYnS/6--