From 24f62e93f314c107b4fa679869e5ba9adb2d545f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 23 Jul 2019 17:04:21 -0400 Subject: [PATCH] Improve psql's \d output for partitioned indexes. Include partitioning information much as we do for partitioned tables. (However, \d+ doesn't show the partition bounds, because those are not stored for indexes.) In passing, fix a couple of queries to look less messy in -E output. Also, add some tests for \d on tables with nondefault tablespaces. (Somebody previously added a rather silly number of tests for \d on partitioned indexes, yet completely neglected other cases.) Justin Pryzby, reviewed by Fabien Coelho Discussion: https://postgr.es/m/20190422154902.GH14223@telsasoft.com --- src/bin/psql/describe.c | 114 +++++++++++----------- src/test/regress/input/tablespace.source | 9 ++ src/test/regress/output/tablespace.source | 89 +++++++++++++++++ 3 files changed, 155 insertions(+), 57 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index b57bb8e831..3e59efe1c5 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2293,8 +2293,13 @@ describeOneTableDetails(const char *schemaname, appendPQExpBufferStr(&tmpbuf, _(", replica identity")); printTableAddFooter(&cont, tmpbuf.data); - add_tablespace_footer(&cont, tableinfo.relkind, - tableinfo.tablespace, true); + + /* + * If it's a partitioned index, we'll print the tablespace below + */ + if (tableinfo.relkind == RELKIND_INDEX) + add_tablespace_footer(&cont, tableinfo.relkind, + tableinfo.tablespace, true); } PQclear(result); @@ -2304,6 +2309,7 @@ describeOneTableDetails(const char *schemaname, tableinfo.relkind == RELKIND_MATVIEW || tableinfo.relkind == RELKIND_FOREIGN_TABLE || tableinfo.relkind == RELKIND_PARTITIONED_TABLE || + tableinfo.relkind == RELKIND_PARTITIONED_INDEX || tableinfo.relkind == RELKIND_TOASTVALUE) { /* Footer information about a table */ @@ -3070,11 +3076,17 @@ describeOneTableDetails(const char *schemaname, tableinfo.relkind == RELKIND_MATVIEW || tableinfo.relkind == RELKIND_FOREIGN_TABLE || tableinfo.relkind == RELKIND_PARTITIONED_TABLE || + tableinfo.relkind == RELKIND_PARTITIONED_INDEX || tableinfo.relkind == RELKIND_TOASTVALUE) { + bool is_partitioned; PGresult *result; int tuples; + /* simplify some repeated tests below */ + is_partitioned = (tableinfo.relkind == RELKIND_PARTITIONED_TABLE || + tableinfo.relkind == RELKIND_PARTITIONED_INDEX); + /* print foreign server name */ if (tableinfo.relkind == RELKIND_FOREIGN_TABLE) { @@ -3115,13 +3127,15 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables (exclude, if parent is a partitioned table) */ + /* print tables inherited from (exclude partitioned parents) */ printfPQExpBuffer(&buf, - "SELECT c.oid::pg_catalog.regclass" - " 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) - " ORDER BY inhseqno;", oid); + "SELECT c.oid::pg_catalog.regclass\n" + "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" + "WHERE c.oid = i.inhparent AND i.inhrelid = '%s'\n" + " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE) + " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_INDEX) + "\nORDER BY inhseqno;", + oid); result = PSQLexec(buf.data); if (!result) @@ -3153,31 +3167,32 @@ 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" - " 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); + "SELECT c.oid::pg_catalog.regclass, c.relkind," + " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n" + "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" + "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n" + "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" - " 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); + "SELECT c.oid::pg_catalog.regclass, c.relkind, NULL\n" + "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" + "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n" + "ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", + oid); else printfPQExpBuffer(&buf, - "SELECT c.oid::pg_catalog.regclass" - " 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); + "SELECT c.oid::pg_catalog.regclass, c.relkind, NULL\n" + "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" + "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n" + "ORDER BY c.relname;", + oid); result = PSQLexec(buf.data); if (!result) goto error_return; - else - tuples = PQntuples(result); + tuples = PQntuples(result); /* * For a partitioned table with no partitions, always print the number @@ -3185,7 +3200,7 @@ 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 (is_partitioned && tuples == 0) { printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples); printTableAddFooter(&cont, buf.data); @@ -3195,49 +3210,34 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); - else + if (is_partitioned) printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ? - _("Child tables") : _("Partitions"); + const char *ct = is_partitioned ? _("Partitions") : _("Child tables"); 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 child_relkind = *PQgetvalue(result, i, 1); - 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", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + if (!PQgetisnull(result, i, 2)) + appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 2)); + if (child_relkind == RELKIND_PARTITIONED_TABLE || + child_relkind == RELKIND_PARTITIONED_INDEX) + appendPQExpBufferStr(&buf, ", PARTITIONED"); if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index 8f012fcc33..a5f61a35dc 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -44,6 +44,10 @@ CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE regress_tblspace; SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c where c.reltablespace = t.oid AND c.relname = 'foo_idx'; +-- check \d output +\d testschema.foo +\d testschema.foo_idx + -- -- partitioned table -- @@ -85,7 +89,12 @@ CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace; 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 +\d+ testschema.part +\d testschema.part1 +\d+ testschema.part1 \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 2ea68cabb0..162b591b31 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -61,6 +61,24 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c foo_idx | regress_tblspace (1 row) +-- check \d output +\d testschema.foo + Table "testschema.foo" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + i | integer | | | +Indexes: + "foo_idx" btree (i), tablespace "regress_tblspace" +Tablespace: "regress_tblspace" + +\d testschema.foo_idx + Index "testschema.foo_idx" + Column | Type | Key? | Definition +--------+---------+------+------------ + i | integer | yes | i +btree, for table "testschema.foo" +Tablespace: "regress_tblspace" + -- -- partitioned table -- @@ -122,12 +140,63 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c part_a_idx | regress_tblspace (3 rows) +\d testschema.part + Partitioned table "testschema.part" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | +Partition key: LIST (a) +Indexes: + "part_a_idx" btree (a), 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) + +\d testschema.part1 + Table "testschema.part1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | +Partition of: testschema.part FOR VALUES IN (1) +Indexes: + "part1_a_idx" btree (a), tablespace "regress_tblspace" + +\d+ testschema.part1 + Table "testschema.part1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | +Partition of: testschema.part FOR VALUES IN (1) +Partition constraint: ((a IS NOT NULL) AND (a = 1)) +Indexes: + "part1_a_idx" btree (a), tablespace "regress_tblspace" + \d testschema.part_a_idx Partitioned index "testschema.part_a_idx" Column | Type | Key? | Definition --------+---------+------+------------ a | integer | yes | a btree, for table "testschema.part" +Number of partitions: 2 (Use \d+ to list them.) +Tablespace: "regress_tblspace" + +\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" +Partitions: testschema.part1_a_idx, + testschema.part2_a_idx Tablespace: "regress_tblspace" -- partitioned rels cannot specify the default tablespace. These fail: @@ -344,6 +413,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" @@ -351,6 +421,7 @@ Partitioned index "testschema.test_index2" --------+--------+------+------------ val | bigint | yes | val btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" \d testschema.test_index3 @@ -359,6 +430,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" @@ -366,6 +438,7 @@ Partitioned index "testschema.test_index4" --------+--------+------+------------ id | bigint | yes | id unique, btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" -- use a custom tablespace for default_tablespace @@ -378,6 +451,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" @@ -385,6 +459,7 @@ Partitioned index "testschema.test_index2" --------+--------+------+------------ val | bigint | yes | val btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" \d testschema.test_index3 @@ -393,6 +468,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" @@ -400,6 +476,7 @@ Partitioned index "testschema.test_index4" --------+--------+------+------------ id | bigint | yes | id unique, btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" SELECT * FROM testschema.test_default_tab_p; @@ -416,6 +493,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" @@ -423,6 +501,7 @@ Partitioned index "testschema.test_index2" --------+---------+------+------------ val | integer | yes | val btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" \d testschema.test_index3 @@ -431,6 +510,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" @@ -438,6 +518,7 @@ Partitioned index "testschema.test_index4" --------+--------+------+------------ id | bigint | yes | id unique, btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" SELECT * FROM testschema.test_default_tab_p; @@ -456,6 +537,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" @@ -463,6 +545,7 @@ Partitioned index "testschema.test_index2" --------+---------+------+------------ val | integer | yes | val btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" \d testschema.test_index3 @@ -471,6 +554,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" @@ -478,6 +562,7 @@ Partitioned index "testschema.test_index4" --------+--------+------+------------ id | bigint | yes | id unique, btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" -- tablespace should not change even if there is an index rewrite @@ -488,6 +573,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" @@ -495,6 +581,7 @@ Partitioned index "testschema.test_index2" --------+--------+------+------------ val | bigint | yes | val btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" \d testschema.test_index3 @@ -503,6 +590,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" @@ -510,6 +598,7 @@ Partitioned index "testschema.test_index4" --------+--------+------+------------ id | bigint | yes | id unique, btree, for table "testschema.test_default_tab_p" +Number of partitions: 1 (Use \d+ to list them.) Tablespace: "regress_tblspace" DROP TABLE testschema.test_default_tab_p; -- 2.40.0