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
>From 237f0bb2a048aa71726eff2580d01404ae3a98b4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
>From 38928b346dc2cc264bb2a7581f1214f14b1bb89a Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
>From bca5b572925c44caafcbcfece41e2c1d979c01f2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
