make \d pg_toast.foo show its indices
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)
Justin
Attachments:
slash-d-show-indices-on-toast.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d7390d5..d26d986 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2274,6 +2274,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 */
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.
--
Regards,
Rafia Sabih
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 ?
postgres=# \d pg_toast.pg_toast_2620
TOAST table "pg_toast.pg_toast_2620"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
FOR TABLE: "pg_catalog.pg_trigger"
Indexes:
"pg_toast_2620_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
That could be displayed differently, perhaps in the header, but I think this is
more consistent with other display.
Justin
Attachments:
v2-0001-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
From 7c15ebe408cc5f2af51120ea152e7997ee768f81 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Fri, 3 May 2019 09:24:51 -0500
Subject: [PATCH v2 1/2] 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 d7390d5..d26d986 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2274,6 +2274,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
v2-0002-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
From 38f50cdb727c67ae7aece8e85caf2960e824cb65 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 30 Apr 2019 19:05:53 -0500
Subject: [PATCH v2 2/2] 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 d26d986..ebdf18a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2152,6 +2152,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
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.
--
Regards,
Rafia Sabih
Rafia Sabih <rafia.pghackers@gmail.com> writes:
On Fri, 3 May 2019 at 16:27, Justin Pryzby <pryzby@telsasoft.com> wrote:
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.
I'm not for that: it's useless information in at least 99.44% of cases.
Possibly it is useful in the other direction as Justin suggests.
Not sure though --- generally, if you're looking at a specific
toast table, you already know which table is its parent. But
maybe confirmation is a good thing.
That seems off-topic for this thread though. I agree with the
stated premise that \d on a toast table should show all the same
information \d on a regular table would.
regards, tom lane
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
Hi,
On 2019-05-06 11:58:18 -0400, Tom Lane wrote:
Not sure though --- generally, if you're looking at a specific
toast table, you already know which table is its parent. But
maybe confirmation is a good thing.
I'm not convinced by that. I've certainly many a time wrote queries
against pg_class to figure out which relation a toast table belongs
to. E.g. after looking at the largest relations in the system, looking
at pg_stat_*_tables, after seeing an error in the logs, etc.
That seems off-topic for this thread though. I agree with the
stated premise that \d on a toast table should show all the same
information \d on a regular table would.
+1
Greetings,
Andres Freund
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
On Mon, May 6, 2019 at 12:26 PM Andres Freund <andres@anarazel.de> wrote:
I'm not convinced by that. I've certainly many a time wrote queries
against pg_class to figure out which relation a toast table belongs
to. E.g. after looking at the largest relations in the system, looking
at pg_stat_*_tables, after seeing an error in the logs, etc.
+1. I think it would be great for \d on the TOAST table to show this
information.
That seems off-topic for this thread though. I agree with the
stated premise that \d on a toast table should show all the same
information \d on a regular table would.+1
That premise seems like a good one, too.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Rafia Sabih <rafia.pghackers@gmail.com> writes:
On Fri, 3 May 2019 at 16:27, Justin Pryzby <pryzby@telsasoft.com> wrote:
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.I'm not for that: it's useless information in at least 99.44% of cases.
I don't think I'd put it in \dt+, but the toast table is still
pg_toast.pg_toast_{relOid}, right? What about showing the OID of the
table in the \d output, eg:
=> \d comments
Table "public.comments" (50788)
Column | Type | Collation | Nullable | Default
etc?
Possibly it is useful in the other direction as Justin suggests.
Not sure though --- generally, if you're looking at a specific
toast table, you already know which table is its parent. But
maybe confirmation is a good thing.
As mentioned elsewhere, there are certainly times when you don't know
that info and if you're looking at the definition of a TOAST table,
which isn't terribly complex, it seems like a good idea to go ahead and
include the table it's the TOAST table for.
That seems off-topic for this thread though. I agree with the
stated premise that \d on a toast table should show all the same
information \d on a regular table would.
+1
Thanks!
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Rafia Sabih <rafia.pghackers@gmail.com> writes:
IMHO, what makes more sense is to show the name of associated toast
table in the \dt+ of the normal table.
I'm not for that: it's useless information in at least 99.44% of cases.
I don't think I'd put it in \dt+, but the toast table is still
pg_toast.pg_toast_{relOid}, right? What about showing the OID of the
table in the \d output, eg:
=> \d comments
Table "public.comments" (50788)
Not unless you want to break every regression test that uses \d.
Instability of the output is also a reason not to show the
toast table's name in the parent's \d[+].
Possibly it is useful in the other direction as Justin suggests.
Not sure though --- generally, if you're looking at a specific
toast table, you already know which table is its parent. But
maybe confirmation is a good thing.
As mentioned elsewhere, there are certainly times when you don't know
that info and if you're looking at the definition of a TOAST table,
which isn't terribly complex, it seems like a good idea to go ahead and
include the table it's the TOAST table for.
I'm not against putting that info into the result of \d on the toast
table.
regards, tom lane
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Rafia Sabih <rafia.pghackers@gmail.com> writes:
IMHO, what makes more sense is to show the name of associated toast
table in the \dt+ of the normal table.I'm not for that: it's useless information in at least 99.44% of cases.
I don't think I'd put it in \dt+, but the toast table is still
pg_toast.pg_toast_{relOid}, right? What about showing the OID of the
table in the \d output, eg:
=> \d comments
Table "public.comments" (50788)Not unless you want to break every regression test that uses \d.
Instability of the output is also a reason not to show the
toast table's name in the parent's \d[+].
So we need a way to turn it off. That doesn't seem like it'd be hard to
implement and the information is certainly quite useful.
Thanks,
Stephen
On Tue, May 7, 2019 at 11:30 AM Stephen Frost <sfrost@snowman.net> wrote:
Not unless you want to break every regression test that uses \d.
Instability of the output is also a reason not to show the
toast table's name in the parent's \d[+].So we need a way to turn it off. That doesn't seem like it'd be hard to
implement and the information is certainly quite useful.
Ugh. It's not really worth it if we have to go to such lengths.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Greetings,
* Robert Haas (robertmhaas@gmail.com) wrote:
On Tue, May 7, 2019 at 11:30 AM Stephen Frost <sfrost@snowman.net> wrote:
Not unless you want to break every regression test that uses \d.
Instability of the output is also a reason not to show the
toast table's name in the parent's \d[+].So we need a way to turn it off. That doesn't seem like it'd be hard to
implement and the information is certainly quite useful.Ugh. It's not really worth it if we have to go to such lengths.
I don't think I agree.. We've gone to pretty great lengths to have
things that can be turned on and off for explain because they're useful
to have but not something that's predictible in the regression tests.
This doesn't strike me as all that different (indeed, if anything it
seems like it should be less of an issue since it's entirely client
side...).
Having our test framework deny us useful features just strikes me as
bizarre.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
Having our test framework deny us useful features just strikes me as
bizarre.
This is presuming that it's useful, which is debatable IMO.
I think most people will find it useless noise almost all of the time.
regards, tom lane
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
Having our test framework deny us useful features just strikes me as
bizarre.This is presuming that it's useful, which is debatable IMO.
I think most people will find it useless noise almost all of the time.
Alright, maybe I'm not the best representation of our user base, but I
sure type 'select oid,* from pg_class where relname = ...' with some
regularity, mostly to get the oid to then go do something else. Having
the relfilenode would be nice too, now that I think about it, and
reltuples. There's ways to get *nearly* everything that's in pg_class
and friends out of various \d incantations, but not quite everything,
which seems unfortunate.
In any case, I can understand an argument that the code it requires is
too much to maintain for a relatively minor feature (though it hardly
seems like it would be...) or that it would be confusing or unhelpful to
users (aka "noise") much of the time, so I'll leave it to others to
comment on if they think any of these ideas be a useful addition or not.
I just don't think we should be voting down a feature because it'd take
a bit of extra effort to make our regression tests work with it, which
is all I was intending to get at here.
Thanks!
Stephen
On Tue, May 7, 2019 at 6:03 PM Stephen Frost <sfrost@snowman.net> wrote:
Alright, maybe I'm not the best representation of our user base, but I
sure type 'select oid,* from pg_class where relname = ...' with some
regularity, mostly to get the oid to then go do something else. Having
the relfilenode would be nice too, now that I think about it, and
reltuples. There's ways to get *nearly* everything that's in pg_class
and friends out of various \d incantations, but not quite everything,
which seems unfortunate.In any case, I can understand an argument that the code it requires is
too much to maintain for a relatively minor feature (though it hardly
seems like it would be...) or that it would be confusing or unhelpful to
users (aka "noise") much of the time, so I'll leave it to others to
comment on if they think any of these ideas be a useful addition or not.I just don't think we should be voting down a feature because it'd take
a bit of extra effort to make our regression tests work with it, which
is all I was intending to get at here.
I think it's unjustifiable to show this in \d output. But maybe in
\d+ output it could be justified, or perhaps in the \d++ which I seem
to recall Alvaro proposing someplace recently.
I think if we're going to show it, it should be on its own line, with
a clear label, not just in the table header as you proposed.
Otherwise, people won't know what it is.
I suppose the work we'd need to make it work with the regression tests
is no worse than the hide_tableam crock which Andres recently added.
That is certainly a crock, but I can testify that it's a very useful
crock for zheap development.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I think it's unjustifiable to show this in \d output. But maybe in
\d+ output it could be justified, or perhaps in the \d++ which I seem
to recall Alvaro proposing someplace recently.
Yeah, if we're going to do that (show a table's toast table) I would
want to bury it in \d++ or some other not-currently-used notation.
regards, tom lane
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:
v3-0000-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
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
v3-0001-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
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
v3-0002-show-childs-of-partitioned-indices.patchtext/x-diff; charset=us-asciiDownload
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
My previous patch missed a 1-line hunk, so resending.
Attachments:
v3-0000-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
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
v3-0001-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
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
v4-0002-show-childs-and-tablespaces-of-partitioned-indice.patchtext/x-diff; charset=us-asciiDownload
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
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.
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:
v5-0001-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
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
v5-0002-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
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
v5-0003-show-childs-of-partitioned-indices.patchtext/x-diff; charset=us-asciiDownload
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
I realized that the test added to show-childs patch was listing partitioned
tables not indices..fixed.
Attachments:
v5-0001-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
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
v5-0002-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
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
v6-0003-show-childs-of-partitioned-indices.patchtext/x-diff; charset=us-asciiDownload
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
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:
v7-0001-print-table-associated-with-given-TOAST-table.patchtext/x-diff; charset=us-asciiDownload
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
v7-0002-make-d-pg_toast.foo-show-its-indices.patchtext/x-diff; charset=us-asciiDownload
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
v7-0003-show-childs-of-partitioned-indices.patchtext/x-diff; charset=us-asciiDownload
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
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.
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
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.