From: Justin Pryzby <pryzby@telsasoft.com>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: Rafia Sabih <rafia.pghackers@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions (and tablespace)
Date: Tue, 16 Jul 2019 00:01:24 -0500
Message-ID: <20190716050124.GG22387@telsasoft.com> (raw)
In-Reply-To: <alpine.DEB.2.21.1906300955550.23641@lancre>
References: <20190422154902.GH14223@telsasoft.com>
<CA+FpmFfihB-okuPa0sxyhALpXcxMuHJYDS95sqvra6+D5Zkk6g@mail.gmail.com>
<20190503142721.GC3925@telsasoft.com>
<CA+FpmFffNcyO8CcG7O7P72K4-PuzataxT=hfWbaV=SrosMK=Cg@mail.gmail.com>
<20190506162251.GN3925@telsasoft.com>
<20190623142546.GE16019@telsasoft.com>
<20190627230201.GA9401@telsasoft.com>
<alpine.DEB.2.21.1906300955550.23641@lancre>
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.patch (text/x-diff, 2.4K) download
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.cindex 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.outindex 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.sqlindex 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.patch (text/x-diff, 1.3K) download
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.cindex 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.outindex 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.patch (text/x-diff, 12.7K) download
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.cindex 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.sourceindex 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.sourceindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, coelho@cri.ensmp.fr, rafia.pghackers@gmail.com
Subject: Re: make \d pg_toast.foo show its indices ; and, \d toast show its main table ; and \d relkind=I show its partitions (and tablespace)
In-Reply-To: <20190716050124.GG22387@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox