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