pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: make \d pg_toast.foo show its indices ; and, \d toast show its main table
11+ messages / 4 participants
[nested] [flat]
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table
@ 2019-05-06 16:22 Justin Pryzby <pryzby@telsasoft.com>
2019-05-06 16:33 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Alvaro Herrera <alvherre@2ndquadrant.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 11+ messages in thread
From: Justin Pryzby @ 2019-05-06 16:22 UTC (permalink / raw)
To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: pgsql-hackers
On Mon, May 06, 2019 at 09:13:52AM +0200, Rafia Sabih wrote:
> On Fri, 3 May 2019 at 16:27, Justin Pryzby <pryzby@telsasoft.com> wrote:
> >
> > 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 ?
> >
> IMHO, what makes more sense is to show the name of associated toast
> table in the \dt+ of the normal table.
Perhaps ... but TOAST is an implementation detail, and I think it should rarely
be important to know the toast table for a given table.
I think it's more useful to go the other way (at least), to answer questions
when pg_toast.* table shows up in a query like these:
- SELECT relpages, relname FROM pg_class ORDER BY 1 DESC;
- SELECT COUNT(1), relname FROM pg_class c JOIN pg_buffercache b ON b.relfilenode=c.oid GROUP BY 2 ORDER BY 1 DESC LIMIT 9;
Justin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
@ 2019-05-06 16:33 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2019-05-06 16:33 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
On 2019-May-06, Justin Pryzby wrote:
> Perhaps ... but TOAST is an implementation detail, and I think it should rarely
> be important to know the toast table for a given table.
I'm with Andres -- while it's admittedly a rare need, it is a real one.
Sometimes I wish for \d++ which would display internal details too obscure
to show in the regular \d+, such as the toast table name.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 11+ messages in thread
* 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)
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
@ 2019-06-23 14:25 ` Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2019-06-23 14:25 UTC (permalink / raw)
To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: pgsql-hackers
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
Attachments:
[text/x-diff] v3-0000-print-table-associated-with-given-TOAST-table.patch (1.3K, ../../20190623142546.GE16019@telsasoft.com/2-v3-0000-print-table-associated-with-given-TOAST-table.patch)
download | inline diff:
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
[text/x-diff] v3-0001-make-d-pg_toast.foo-show-its-indices.patch (806B, ../../20190623142546.GE16019@telsasoft.com/3-v3-0001-make-d-pg_toast.foo-show-its-indices.patch)
download | inline diff:
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
[text/x-diff] v3-0002-show-childs-of-partitioned-indices.patch (3.9K, ../../20190623142546.GE16019@telsasoft.com/4-v3-0002-show-childs-of-partitioned-indices.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* 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)
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
@ 2019-06-27 23:02 ` Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2019-06-27 23:02 UTC (permalink / raw)
To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: pgsql-hackers
My previous patch missed a 1-line hunk, so resending.
Attachments:
[text/x-diff] v3-0000-print-table-associated-with-given-TOAST-table.patch (1.3K, ../../20190627230201.GA9401@telsasoft.com/2-v3-0000-print-table-associated-with-given-TOAST-table.patch)
download | inline diff:
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
[text/x-diff] v3-0001-make-d-pg_toast.foo-show-its-indices.patch (806B, ../../20190627230201.GA9401@telsasoft.com/3-v3-0001-make-d-pg_toast.foo-show-its-indices.patch)
download | inline diff:
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
[text/x-diff] v4-0002-show-childs-and-tablespaces-of-partitioned-indice.patch (4.3K, ../../20190627230201.GA9401@telsasoft.com/4-v4-0002-show-childs-and-tablespaces-of-partitioned-indice.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* 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)
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
@ 2019-06-30 08:26 ` Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 11+ messages in thread
From: Fabien COELHO @ 2019-06-30 08:26 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
There are 3 independent patches associated to one thread and one CF entry.
*** About toast table v3:
Patch applies cleanly, compiles, works for me.
ISTM that the he query should be unambiguous: pg_catalog.pg_class instead
of pg_class, add an alias (eg c), use c.FIELD to access an attribute. In
its current form "pg_class" could resolve to another table depending on
the search path.
C style is broken. On "if () {", brace must be on next line. On "1 !=
PQntuples(result)", I would exchange operands.
PQclear must be called on the main path.
If the table name contains a ", the result looks awkward:
For table: "public.foo"bla"
I'm wondering whether some escaping should be done. Well, it is not done
for other simular entries, so probably this is bad but okay:-)
There are no tests:-(
*** About toast index v3
Patch applies cleanly, compiles, works for me.
There are no tests:-(
*** About the next one, v4
Patch applies cleanly, compiles. Not sure how to test it.
"switch (*PQgetvalue(result, i, 2))": I understand that relkind is a must
admit I do not like this style much, an intermediate variable would
improve readability. Also, a simple if instead of a swich might be more
appropriate, and be closer to the previous implementation.
There are no tests:-(
--
Fabien.
^ permalink raw reply [nested|flat] 11+ messages in thread
* 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)
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
@ 2019-07-16 05:01 ` Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2019-07-16 05:01 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
Sorry, I missed this until now.
On Sun, Jun 30, 2019 at 10:26:28AM +0200, Fabien COELHO wrote:
> *** About toast table v3:
>
> Patch applies cleanly, compiles, works for me.
>
> ISTM that the he query should be unambiguous: pg_catalog.pg_class instead of
> pg_class, add an alias (eg c), use c.FIELD to access an attribute. In its
> current form "pg_class" could resolve to another table depending on the
> search path.
Thanks for noticing, fixed.
> C style is broken. On "if () {", brace must be on next line. On "1 !=
> PQntuples(result)", I would exchange operands.
>
> PQclear must be called on the main path.
Done
> There are no tests:-(
"show-childs" caused plenty of tests fail; actually..it looks like my previous
patch duplicated "tablespace" line for indices (and I managed to not notice the
original one, and claimed my patch fixed that omission, sigh). I added test
that it shows its partitions, too.
It seems like an obviously good idea to add tests for \d toast; it's not clear
to me how to do run \d for a toast table, which is named after a user table's
OID... (I tested that \gexec doesn't work for this).
So for now I used \d pg_toast.pg_toast_2619
> If the table name contains a ", the result looks awkward:
>
> For table: "public.foo"bla"
>
> I'm wondering whether some escaping should be done. Well, it is not done for
> other simular entries, so probably this is bad but okay:-)
Leaving this for another commit-day.
Thanks for testing.
Justin
Attachments:
[text/x-diff] v5-0001-print-table-associated-with-given-TOAST-table.patch (2.4K, ../../20190716050124.GG22387@telsasoft.com/2-v5-0001-print-table-associated-with-given-TOAST-table.patch)
download | inline diff:
From 237f0bb2a048aa71726eff2580d01404ae3a98b4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v5] print table associated with given TOAST table
---
src/bin/psql/describe.c | 28 ++++++++++++++++++++++++++++
src/test/regress/expected/psql.out | 10 ++++++++++
src/test/regress/sql/psql.sql | 3 +++
3 files changed, 41 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3ee9c82..13ed2e1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2153,6 +2153,34 @@ describeOneTableDetails(const char *schemaname,
}
}
+ /* print table associated with given TOAST table */
+ if (tableinfo.relkind == RELKIND_TOASTVALUE)
+ {
+ PGresult *result = NULL;
+ printfPQExpBuffer(&buf,
+ "SELECT c.relnamespace::pg_catalog.regnamespace, c.relname FROM pg_catalog.pg_class c WHERE reltoastrelid = '%s'",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result)
+ {
+ goto error_return;
+ }
+ else if (PQntuples(result) != 1)
+ {
+ 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);
+ PQclear(result);
+ }
+ }
+
if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
/* Get the partition key information */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 9021c80..5c8e439 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4748,3 +4748,13 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role regress_partitioning_role;
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
+TOAST table "pg_toast.pg_toast_2619"
+ Column | Type
+------------+---------
+ chunk_id | oid
+ chunk_seq | integer
+ chunk_data | bytea
+For table: "pg_catalog.pg_statistic"
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index cefe41b..b4a232d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1131,3 +1131,6 @@ set search_path to default;
set role to default;
drop role regress_partitioning_role;
+
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
--
2.7.4
[text/x-diff] v5-0002-make-d-pg_toast.foo-show-its-indices.patch (1.3K, ../../20190716050124.GG22387@telsasoft.com/3-v5-0002-make-d-pg_toast.foo-show-its-indices.patch)
download | inline diff:
From 38928b346dc2cc264bb2a7581f1214f14b1bb89a Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v5] make \d pg_toast.foo show its indices
---
src/bin/psql/describe.c | 1 +
src/test/regress/expected/psql.out | 2 ++
2 files changed, 3 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 13ed2e1..86a7610 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2303,6 +2303,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 */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 5c8e439..d53dbb0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4757,4 +4757,6 @@ TOAST table "pg_toast.pg_toast_2619"
chunk_seq | integer
chunk_data | bytea
For table: "pg_catalog.pg_statistic"
+Indexes:
+ "pg_toast_2619_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
--
2.7.4
[text/x-diff] v5-0003-show-childs-of-partitioned-indices.patch (12.7K, ../../20190716050124.GG22387@telsasoft.com/4-v5-0003-show-childs-of-partitioned-indices.patch)
download | inline diff:
From bca5b572925c44caafcbcfece41e2c1d979c01f2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 19 Jun 2019 15:41:25 -0500
Subject: [PATCH v5] show childs of partitioned indices
---
src/bin/psql/describe.c | 57 ++++++++++++++-----------------
src/test/regress/input/tablespace.source | 1 +
src/test/regress/output/tablespace.source | 32 +++++++++++++++++
3 files changed, 58 insertions(+), 32 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 86a7610..6d136ba 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3069,6 +3069,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;
@@ -3120,6 +3121,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);
@@ -3184,7 +3186,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);
@@ -3194,7 +3197,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);
@@ -3204,39 +3207,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, ',');
@@ -3279,10 +3276,6 @@ describeOneTableDetails(const char *schemaname,
if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
printTableAddFooter(&cont, _("Has OIDs: yes"));
- /* Tablespace info */
- add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
- true);
-
/* Access method info */
if (verbose && tableinfo.relam != NULL && !pset.hide_tableam)
{
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 8f012fc..4e11b91 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -86,6 +86,7 @@ CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx';
\d testschema.part_a_idx
+\d+ testschema.part
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 2ea68ca..deff35a 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -129,6 +129,18 @@ Partitioned index "testschema.part_a_idx"
a | integer | yes | a
btree, for table "testschema.part"
Tablespace: "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d+ testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a), tablespace "regress_tblspace"
+Partitions: testschema.part1 FOR VALUES IN (1),
+ testschema.part2 FOR VALUES IN (2)
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
@@ -344,6 +356,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -352,6 +365,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -359,6 +373,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -367,6 +382,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- use a custom tablespace for default_tablespace
SET default_tablespace TO regress_tblspace;
@@ -378,6 +394,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -386,6 +403,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -393,6 +411,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -401,6 +420,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -416,6 +436,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -424,6 +445,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -431,6 +453,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -439,6 +462,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -456,6 +480,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -464,6 +489,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -471,6 +497,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -479,6 +506,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
@@ -488,6 +516,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -496,6 +525,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -503,6 +533,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -511,6 +542,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
DROP TABLE testschema.test_default_tab_p;
-- check that default_tablespace affects index additions in ALTER TABLE
--
2.7.4
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
@ 2019-07-16 14:06 ` Justin Pryzby <pryzby@telsasoft.com>
2019-07-17 06:26 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2019-07-16 14:06 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
I realized that the test added to show-childs patch was listing partitioned
tables not indices..fixed.
Attachments:
[text/x-diff] v5-0001-print-table-associated-with-given-TOAST-table.patch (2.4K, ../../20190716140636.GA11917@telsasoft.com/2-v5-0001-print-table-associated-with-given-TOAST-table.patch)
download | inline diff:
From 237f0bb2a048aa71726eff2580d01404ae3a98b4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v5] print table associated with given TOAST table
---
src/bin/psql/describe.c | 28 ++++++++++++++++++++++++++++
src/test/regress/expected/psql.out | 10 ++++++++++
src/test/regress/sql/psql.sql | 3 +++
3 files changed, 41 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3ee9c82..13ed2e1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2153,6 +2153,34 @@ describeOneTableDetails(const char *schemaname,
}
}
+ /* print table associated with given TOAST table */
+ if (tableinfo.relkind == RELKIND_TOASTVALUE)
+ {
+ PGresult *result = NULL;
+ printfPQExpBuffer(&buf,
+ "SELECT c.relnamespace::pg_catalog.regnamespace, c.relname FROM pg_catalog.pg_class c WHERE reltoastrelid = '%s'",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result)
+ {
+ goto error_return;
+ }
+ else if (PQntuples(result) != 1)
+ {
+ 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);
+ PQclear(result);
+ }
+ }
+
if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
/* Get the partition key information */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 9021c80..5c8e439 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4748,3 +4748,13 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role regress_partitioning_role;
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
+TOAST table "pg_toast.pg_toast_2619"
+ Column | Type
+------------+---------
+ chunk_id | oid
+ chunk_seq | integer
+ chunk_data | bytea
+For table: "pg_catalog.pg_statistic"
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index cefe41b..b4a232d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1131,3 +1131,6 @@ set search_path to default;
set role to default;
drop role regress_partitioning_role;
+
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
--
2.7.4
[text/x-diff] v5-0002-make-d-pg_toast.foo-show-its-indices.patch (1.3K, ../../20190716140636.GA11917@telsasoft.com/3-v5-0002-make-d-pg_toast.foo-show-its-indices.patch)
download | inline diff:
From 38928b346dc2cc264bb2a7581f1214f14b1bb89a Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v5] make \d pg_toast.foo show its indices
---
src/bin/psql/describe.c | 1 +
src/test/regress/expected/psql.out | 2 ++
2 files changed, 3 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 13ed2e1..86a7610 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2303,6 +2303,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 */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 5c8e439..d53dbb0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4757,4 +4757,6 @@ TOAST table "pg_toast.pg_toast_2619"
chunk_seq | integer
chunk_data | bytea
For table: "pg_catalog.pg_statistic"
+Indexes:
+ "pg_toast_2619_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
--
2.7.4
[text/x-diff] v6-0003-show-childs-of-partitioned-indices.patch (12.5K, ../../20190716140636.GA11917@telsasoft.com/4-v6-0003-show-childs-of-partitioned-indices.patch)
download | inline diff:
From ae748eda1460e6da37a6c5a1e1168a5c7c18639b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 19 Jun 2019 15:41:25 -0500
Subject: [PATCH v6] show childs of partitioned indices
---
src/bin/psql/describe.c | 57 ++++++++++++++-----------------
src/test/regress/input/tablespace.source | 1 +
src/test/regress/output/tablespace.source | 31 +++++++++++++++++
3 files changed, 57 insertions(+), 32 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 86a7610..6d136ba 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3069,6 +3069,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;
@@ -3120,6 +3121,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);
@@ -3184,7 +3186,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);
@@ -3194,7 +3197,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);
@@ -3204,39 +3207,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, ',');
@@ -3279,10 +3276,6 @@ describeOneTableDetails(const char *schemaname,
if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
printTableAddFooter(&cont, _("Has OIDs: yes"));
- /* Tablespace info */
- add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
- true);
-
/* Access method info */
if (verbose && tableinfo.relam != NULL && !pset.hide_tableam)
{
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 8f012fc..7875292 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -86,6 +86,7 @@ CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx';
\d testschema.part_a_idx
+\d+ testschema.part_a_idx
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 2ea68ca..d4771b7 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -129,6 +129,17 @@ Partitioned index "testschema.part_a_idx"
a | integer | yes | a
btree, for table "testschema.part"
Tablespace: "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d+ testschema.part_a_idx
+ Partitioned index "testschema.part_a_idx"
+ Column | Type | Key? | Definition | Storage | Stats target
+--------+---------+------+------------+---------+--------------
+ a | integer | yes | a | plain |
+btree, for table "testschema.part"
+Tablespace: "regress_tblspace"
+Partitions: testschema.part1_a_idx,
+ testschema.part2_a_idx
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
@@ -344,6 +355,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -352,6 +364,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -359,6 +372,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -367,6 +381,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- use a custom tablespace for default_tablespace
SET default_tablespace TO regress_tblspace;
@@ -378,6 +393,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -386,6 +402,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -393,6 +410,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -401,6 +419,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -416,6 +435,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -424,6 +444,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -431,6 +452,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -439,6 +461,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -456,6 +479,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -464,6 +488,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -471,6 +496,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -479,6 +505,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
@@ -488,6 +515,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -496,6 +524,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -503,6 +532,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -511,6 +541,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
DROP TABLE testschema.test_default_tab_p;
-- check that default_tablespace affects index additions in ALTER TABLE
--
2.7.4
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
@ 2019-07-17 06:26 ` Justin Pryzby <pryzby@telsasoft.com>
2019-07-23 07:19 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2019-07-17 06:26 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
Find attached updated patches which also work against old servers.
1) avoid ::regnamespace; 2) don't PQgetvalue() fields which don't exist and then crash.
Attachments:
[text/x-diff] v7-0001-print-table-associated-with-given-TOAST-table.patch (2.4K, ../../20190717062634.GV22387@telsasoft.com/2-v7-0001-print-table-associated-with-given-TOAST-table.patch)
download | inline diff:
From 16b31dc1e4142ed6d0f5f7ed6d65c6184f546a3c Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v7] print table associated with given TOAST table
---
src/bin/psql/describe.c | 29 +++++++++++++++++++++++++++++
src/test/regress/expected/psql.out | 10 ++++++++++
src/test/regress/sql/psql.sql | 3 +++
3 files changed, 42 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3ee9c82..9cd2e7d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2153,6 +2153,35 @@ describeOneTableDetails(const char *schemaname,
}
}
+ /* print table associated with given TOAST table */
+ if (tableinfo.relkind == RELKIND_TOASTVALUE)
+ {
+ PGresult *result = NULL;
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname, c.relname FROM pg_catalog.pg_class c"
+ " JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
+ " WHERE reltoastrelid='%s'", oid);
+ result = PSQLexec(buf.data);
+ if (!result)
+ {
+ goto error_return;
+ }
+ else if (PQntuples(result) != 1)
+ {
+ 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);
+ PQclear(result);
+ }
+ }
+
if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
/* Get the partition key information */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 9021c80..5c8e439 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4748,3 +4748,13 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role regress_partitioning_role;
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
+TOAST table "pg_toast.pg_toast_2619"
+ Column | Type
+------------+---------
+ chunk_id | oid
+ chunk_seq | integer
+ chunk_data | bytea
+For table: "pg_catalog.pg_statistic"
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index cefe41b..b4a232d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1131,3 +1131,6 @@ set search_path to default;
set role to default;
drop role regress_partitioning_role;
+
+-- slash dee on toast table:
+\d pg_toast.pg_toast_2619
--
2.7.4
[text/x-diff] v7-0002-make-d-pg_toast.foo-show-its-indices.patch (1.3K, ../../20190717062634.GV22387@telsasoft.com/3-v7-0002-make-d-pg_toast.foo-show-its-indices.patch)
download | inline diff:
From e862e90f9ab0504d43728df93ed589b52786e29b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v7] make \d pg_toast.foo show its indices
---
src/bin/psql/describe.c | 1 +
src/test/regress/expected/psql.out | 2 ++
2 files changed, 3 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 9cd2e7d..b3b94d1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2304,6 +2304,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 */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 5c8e439..d53dbb0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4757,4 +4757,6 @@ TOAST table "pg_toast.pg_toast_2619"
chunk_seq | integer
chunk_data | bytea
For table: "pg_catalog.pg_statistic"
+Indexes:
+ "pg_toast_2619_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
--
2.7.4
[text/x-diff] v7-0003-show-childs-of-partitioned-indices.patch (13.9K, ../../20190717062634.GV22387@telsasoft.com/4-v7-0003-show-childs-of-partitioned-indices.patch)
download | inline diff:
From 1297e4eb56c553dca6c46e1caf911470ae0c5eb4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 19 Jun 2019 15:41:25 -0500
Subject: [PATCH v7] show childs of partitioned indices
---
src/bin/psql/describe.c | 62 ++++++++++++-------------------
src/test/regress/input/tablespace.source | 1 +
src/test/regress/output/tablespace.source | 31 ++++++++++++++++
3 files changed, 56 insertions(+), 38 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b3b94d1..9595c65 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3070,6 +3070,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;
@@ -3121,6 +3122,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);
@@ -3153,22 +3155,21 @@ describeOneTableDetails(const char *schemaname,
/* print child tables (with additional info if partitions) */
if (pset.sversion >= 100000)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass,"
- " pg_catalog.pg_get_expr(c.relpartbound, c.oid),"
- " c.relkind"
+ "SELECT c.oid::pg_catalog.regclass, c.relkind,"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid)"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
" ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
" c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
else if (pset.sversion >= 80300)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass"
+ "SELECT c.oid::pg_catalog.regclass, c.relkind, ''"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
else
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass"
+ "SELECT c.oid::pg_catalog.regclass, c.relkind, ''"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
" ORDER BY c.relname;", oid);
@@ -3185,7 +3186,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);
@@ -3195,7 +3197,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);
@@ -3205,39 +3207,27 @@ 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));
- }
- else
- {
- char *partitioned_note;
-
- if (*PQgetvalue(result, i, 2) == RELKIND_PARTITIONED_TABLE)
- partitioned_note = ", PARTITIONED";
- else
- partitioned_note = "";
+ char relkind = *PQgetvalue(result, i, 1);
+ char *partitioned_note = (relkind==RELKIND_PARTITIONED_INDEX ||
+ relkind==RELKIND_PARTITIONED_TABLE) ? ", PARTITIONED" : "";
+ char *ptn_expr = tableinfo.relkind == RELKIND_PARTITIONED_TABLE ? PQgetvalue(result, i, 2) : "";
- 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, ',');
@@ -3280,10 +3270,6 @@ describeOneTableDetails(const char *schemaname,
if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
printTableAddFooter(&cont, _("Has OIDs: yes"));
- /* Tablespace info */
- add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
- true);
-
/* Access method info */
if (verbose && tableinfo.relam != NULL && !pset.hide_tableam)
{
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 8f012fc..7875292 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -86,6 +86,7 @@ CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx';
\d testschema.part_a_idx
+\d+ testschema.part_a_idx
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 2ea68ca..d4771b7 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -129,6 +129,17 @@ Partitioned index "testschema.part_a_idx"
a | integer | yes | a
btree, for table "testschema.part"
Tablespace: "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d+ testschema.part_a_idx
+ Partitioned index "testschema.part_a_idx"
+ Column | Type | Key? | Definition | Storage | Stats target
+--------+---------+------+------------+---------+--------------
+ a | integer | yes | a | plain |
+btree, for table "testschema.part"
+Tablespace: "regress_tblspace"
+Partitions: testschema.part1_a_idx,
+ testschema.part2_a_idx
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
@@ -344,6 +355,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -352,6 +364,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -359,6 +372,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -367,6 +381,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- use a custom tablespace for default_tablespace
SET default_tablespace TO regress_tblspace;
@@ -378,6 +393,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -386,6 +402,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -393,6 +410,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -401,6 +419,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -416,6 +435,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -424,6 +444,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -431,6 +452,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -439,6 +461,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
SELECT * FROM testschema.test_default_tab_p;
id | val
@@ -456,6 +479,7 @@ Partitioned index "testschema.test_index1"
--------+---------+------+------------
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -464,6 +488,7 @@ Partitioned index "testschema.test_index2"
val | integer | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -471,6 +496,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -479,6 +505,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
@@ -488,6 +515,7 @@ Partitioned index "testschema.test_index1"
--------+--------+------+------------
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index2
Partitioned index "testschema.test_index2"
@@ -496,6 +524,7 @@ Partitioned index "testschema.test_index2"
val | bigint | yes | val
btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index3
Partitioned index "testschema.test_index3"
@@ -503,6 +532,7 @@ Partitioned index "testschema.test_index3"
--------+--------+------+------------
id | bigint | yes | id
primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
\d testschema.test_index4
Partitioned index "testschema.test_index4"
@@ -511,6 +541,7 @@ Partitioned index "testschema.test_index4"
id | bigint | yes | id
unique, btree, for table "testschema.test_default_tab_p"
Tablespace: "regress_tblspace"
+Number of partitions: 1 (Use \d+ to list them.)
DROP TABLE testschema.test_default_tab_p;
-- check that default_tablespace affects index additions in ALTER TABLE
--
2.7.4
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-17 06:26 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
@ 2019-07-23 07:19 ` Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-23 21:08 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Fabien COELHO @ 2019-07-23 07:19 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
> Find attached updated patches which also work against old servers.
I can't check that for sure.
* About toast table addition v7:
Patch applies cleanly, compiles, make check ok, no doc.
This addition show the main table of a toast table, which is useful.
Field relnamespace oid in pg_class appears with pg 7.3, maybe it would be
appropriate to guard agains older versions, with "pset.sversion >= 70300".
It seems that there are other unguarded instances in "describe.c", so
maybe this is considered too old.
Test is ok.
* About toast index v7:
Patch applies cleanly on top of previous, compiles, make check ok, no doc.
This patch simply enables an existing query on toast tables so as to show
corresponding indices.
Test is ok.
* About toast part v7.
Patch applies cleanly, compiles, make check ok, no doc.
It gives the partition info about an index as it is shown about a table,
which is useful.
There are some changes in the query on older systems, which seem harmless.
The code is rather simplified because a special case is removed, which is
a good thing.
Test is ok.
Marked as ready.
--
Fabien.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-17 06:26 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-23 07:19 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
@ 2019-07-23 21:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-23 22:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2019-07-23 21:08 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Field relnamespace oid in pg_class appears with pg 7.3, maybe it would be
> appropriate to guard agains older versions, with "pset.sversion >= 70300".
> It seems that there are other unguarded instances in "describe.c", so
> maybe this is considered too old.
Per the comment at the head of describe.c, we only expect it to work
back to 7.4. I tested against a 7.4 server, the modified queries
seem fine.
> Marked as ready.
Pushed with minor fiddling with the toast-table code, and rather
more significant hacking on the partitioned-index code. Notably,
0003 had broken output of Tablespace: footers for everything except
indexes. It's possibly not Justin's fault that that wasn't noticed,
because we had no regression tests covering it :-(. We do now.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-17 06:26 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-23 07:19 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-23 21:08 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-07-23 22:06 ` Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 0 replies; 11+ messages in thread
From: Fabien COELHO @ 2019-07-23 22:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Rafia Sabih <rafia.pghackers@gmail.com>; pgsql-hackers
> Pushed with minor fiddling with the toast-table code, and rather
> more significant hacking on the partitioned-index code. Notably,
> 0003 had broken output of Tablespace: footers for everything except
> indexes.
Argh, sorry for the review miss.
> It's possibly not Justin's fault that that wasn't noticed,
> because we had no regression tests covering it :-(. We do now.
Thanks.
--
Fabien.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2019-07-23 22:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-06 16:22 Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table Justin Pryzby <pryzby@telsasoft.com>
2019-05-06 16:33 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-06-23 14:25 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-27 23:02 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-06-30 08:26 ` 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) Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-16 05:01 ` 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) Justin Pryzby <pryzby@telsasoft.com>
2019-07-16 14:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-17 06:26 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Justin Pryzby <pryzby@telsasoft.com>
2019-07-23 07:19 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
2019-07-23 21:08 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Tom Lane <tgl@sss.pgh.pa.us>
2019-07-23 22:06 ` Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions Fabien COELHO <coelho@cri.ensmp.fr>
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