pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedallow to \dtS+ pg_toast.*
8+ messages / 3 participants
[nested] [flat]
* allow to \dtS+ pg_toast.*
@ 2020-11-30 16:54 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2020-11-30 16:54 UTC (permalink / raw)
To: pgsql-hackers
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
--
Justin
Attachments:
[text/x-diff] v1-0001-Allow-to-dt-pg_toast.patch (2.6K, ../../20201130165436.GX24052@telsasoft.com/2-v1-0001-Allow-to-dt-pg_toast.patch)
download | inline diff:
From 99317e354e0a2744d7f72930773ab4cf78733f26 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 28 Oct 2020 23:51:39 -0500
Subject: [PATCH v1] Allow to \dt+ pg_toast.*
---
src/bin/psql/describe.c | 14 ++++++--------
1 file changed, 6 insertions(+), 8 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e9c10ced62..e00361a84b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3758,6 +3758,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'"
@@ -3771,6 +3772,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"),
@@ -3866,6 +3868,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) ",");
@@ -3876,14 +3880,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\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");
+ if (!showSystem)
+ appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
--
2.17.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2020-12-17 15:16 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2020-12-17 15:16 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; pgsql-hackers
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.
The patch passes regression tests.
While playing around with it, I found the following oddity:
regression=# \dtS+ pg_toast.pg_toast_30701
List of relations
Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
----------+----------------+-------------+---------+-------------+---------------+---------+-------------
pg_toast | pg_toast_30701 | toast table | laurenz | permanent | heap | 0 bytes |
(1 row)
regression=# \dtS pg_toast.pg_toast_30701
List of relations
Schema | Name | Type | Owner
----------+----------------+-------------+---------
pg_toast | pg_toast_30701 | toast table | laurenz
(1 row)
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.
Another thing that is missing is tab completion for
regression=# \dtS pg_toast.pg_
This should work just like for \d and \dS.
Both of these effects can also be observed with \di and toast indexes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2020-12-18 06:58 Justin Pryzby <pryzby@telsasoft.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2020-12-18 06:58 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-hackers
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.
Attachments:
[text/x-diff] 0001-Allow-to-dti-pg_toast.patch (3.6K, ../../20201218065808.GY30237@telsasoft.com/2-0001-Allow-to-dti-pg_toast.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2020-12-18 11:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2020-12-18 11:43 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-hackers
On Fri, 2020-12-18 at 00:58 -0600, Justin Pryzby wrote:
> 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.
Yes, exactly.
I wonder why the modification in "listPartitionedTables" is necessary.
Surely there cannot be any partitioned toast tables, can there?
> > 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
That was about VACUUM and ANALYZE, and I certainly see the point
that this is not a frequent enough use case to warrant guiding
people there with tab completion.
But I don't take that as an argument against tab completion in our case.
If I want to know how big the TOAST table of relation 87654 is,
I think it is convenient if I can tab to
\dt+ pg_toast.pg_toast_
If you want to abolish special treatment for TOAST tables in \dt(S),
go all the way.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2020-12-18 17:33 Justin Pryzby <pryzby@telsasoft.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2020-12-18 17:33 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-hackers
On Fri, Dec 18, 2020 at 12:43:07PM +0100, Laurenz Albe wrote:
> On Fri, 2020-12-18 at 00:58 -0600, Justin Pryzby wrote:
> > 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.
>
> Yes, exactly.
>
> I wonder why the modification in "listPartitionedTables" is necessary.
> Surely there cannot be any partitioned toast tables, can there?
The comment should be removed for consistency.
And I changed the code for consistency with listTables (from which I assume
listPartitionedTables was derived - I was involved in the last stages of that
patch). It doesn't need to exclude pg_catalog or information_schema, either,
but it's kept the same for consistency. That part could also be removed.
> > > Another thing that is missing is tab completion for
> > > regression=# \dtS pg_toast.pg_
> > > This should work just like for \d and \dS.
..
> If I want to know how big the TOAST table of relation 87654 is,
> I think it is convenient if I can tab to
>
> \dt+ pg_toast.pg_toast_
I agree that it's nice to complete the schema name, but I'm still not convinced
this part should be included.
The way to include pg_toast.pg_toast is if toast relations are included, which
is exactly what Tom pointed out is usually unhelpful. If you include toast
relations, tab completion might give "pg_toast.pg_toast_14..." when you wanted
to paste "145678" - you'd need to remove the common suffix that it found.
I considered whether "toast table" should be capitalized (as it is for "\d")
but I think it should stay lowercase.
--
Justin
Attachments:
[text/x-diff] 0001-Allow-to-dti-pg_toast.patch (5.1K, ../../20201218173312.GZ30237@telsasoft.com/2-0001-Allow-to-dti-pg_toast.patch)
download | inline diff:
From 987bbcb083507426018bfccd1eae7552468c57cb 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 ++++++------------------
src/bin/psql/tab-complete.c | 13 ++++++++++++-
2 files changed, 18 insertions(+), 19 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)");
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8d6f93147e..58b4d55a96 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -453,6 +453,17 @@ static const SchemaQuery Query_for_list_of_tables = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_tables_with_toast = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition =
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_TOASTVALUE) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "pg_catalog.quote_ident(c.relname)",
+};
+
static const SchemaQuery Query_for_list_of_partitioned_tables = {
.catname = "pg_catalog.pg_class c",
.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -3903,7 +3914,7 @@ psql_completion(const char *text, int start, int end)
else if (TailMatchesCS("\\ds*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
else if (TailMatchesCS("\\dt*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables_with_toast, NULL);
else if (TailMatchesCS("\\dT*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
--
2.17.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2020-12-21 14:20 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2020-12-21 14:20 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-hackers
On Fri, 2020-12-18 at 11:33 -0600, Justin Pryzby wrote:
> > > > > This makes toast tables a bit less special and easier to inspect.
> >
> > I wonder why the modification in "listPartitionedTables" is necessary.
> > Surely there cannot be any partitioned toast tables, can there?
>
> The comment should be removed for consistency.
> And I changed the code for consistency with listTables (from which I assume
> listPartitionedTables was derived - I was involved in the last stages of that
> patch). It doesn't need to exclude pg_catalog or information_schema, either,
> but it's kept the same for consistency. That part could also be removed.
I don't think that consistency with "listTables" is particularly useful here,
but I think this is somewhat academic.
I'll leave that for the committer to decide.
> > > > Another thing that is missing is tab completion for
> > > > regression=# \dtS pg_toast.pg_
> > > > This should work just like for \d and \dS.
>
> I agree that it's nice to complete the schema name, but I'm still not convinced
> this part should be included.
>
> The way to include pg_toast.pg_toast is if toast relations are included, which
> is exactly what Tom pointed out is usually unhelpful. If you include toast
> relations, tab completion might give "pg_toast.pg_toast_14..." when you wanted
> to paste "145678" - you'd need to remove the common suffix that it found.
Again a judgement call. I am happy with the way the latest patch does it.
> I considered whether "toast table" should be capitalized (as it is for "\d")
> but I think it should stay lowercase.
Then you should also change the way \d does it (upper case).
I think we should be consistent.
I'd use TOAST for both to create no unnecessary change in \d output.
Anyway, I think that this is ready for committer and will mark it as such.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2021-01-05 23:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-01-05 23:46 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-hackers
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2020-12-18 at 11:33 -0600, Justin Pryzby wrote:
>>>> This makes toast tables a bit less special and easier to inspect.
Pushed, except for
>>> Another thing that is missing is tab completion for
>>> regression=# \dtS pg_toast.pg_
>>> This should work just like for \d and \dS.
>> I agree that it's nice to complete the schema name, but I'm still not convinced
>> this part should be included.
> Again a judgement call. I am happy with the way the latest patch does it.
I remain pretty much against including toast tables in tab completion,
so I left out that part.
>> I considered whether "toast table" should be capitalized (as it is for "\d")
>> but I think it should stay lowercase.
> Then you should also change the way \d does it (upper case).
> I think we should be consistent.
I think capitalized TOAST is correct, because it's an acronym.
I also agree that consistency with what \d shows is important,
and I have no desire to change \d's longstanding output.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: allow to \dtS+ pg_toast.*
@ 2021-01-05 23:50 Justin Pryzby <pryzby@telsasoft.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2021-01-05 23:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-hackers
On Tue, Jan 05, 2021 at 06:46:01PM -0500, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Fri, 2020-12-18 at 11:33 -0600, Justin Pryzby wrote:
> >>>> This makes toast tables a bit less special and easier to inspect.
>
> Pushed, except for
Thank you
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-01-05 23:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-30 16:54 allow to \dtS+ pg_toast.* Justin Pryzby <pryzby@telsasoft.com>
2020-12-17 15:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-12-18 06:58 ` Justin Pryzby <pryzby@telsasoft.com>
2020-12-18 11:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-12-18 17:33 ` Justin Pryzby <pryzby@telsasoft.com>
2020-12-21 14:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-05 23:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-05 23:50 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox