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.
>From 16b31dc1e4142ed6d0f5f7ed6d65c6184f546a3c Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
>From e862e90f9ab0504d43728df93ed589b52786e29b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
>From 1297e4eb56c553dca6c46e1caf911470ae0c5eb4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
