From: Nikita Glukhov Date: Mon, 15 Jul 2019 17:11:06 +0300 Subject: [PATCH 2/2] Add psql index info commands --- doc/src/sgml/ref/psql-ref.sgml | 28 ++++ src/bin/psql/command.c | 10 ++ src/bin/psql/describe.c | 291 ++++++++++++++++++++++++++++++++++++- src/bin/psql/describe.h | 7 + src/bin/psql/help.c | 2 + src/bin/psql/tab-complete.c | 5 +- src/test/regress/expected/psql.out | 16 ++ src/test/regress/sql/psql.sql | 3 + 8 files changed, 359 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e690c4d..c4ff542 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1451,6 +1451,34 @@ testdb=> + + \dip [ pattern ] + + + + Shows index properties listed in + . + If pattern is + specified, only access methods whose names match the pattern are shown. + + + + + + + \dicp [ pattern] + + + + + + Shows index column properties listed in + . + If pattern is + specified, only access methods whose names match the pattern are shown. + + + \des[+] [ pattern ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index e6cb260..36e2efe 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -827,6 +827,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'v': case 'm': case 'i': + if (strncmp(cmd, "dip", 3) == 0) + { + success = describeIndexProperties(pattern, show_system); + break; + } + else if (strncmp(cmd, "dicp", 4) == 0) + { + success = describeIndexColumnProperties(pattern, show_system); + break; + } case 's': case 'E': success = listTables(&cmd[1], pattern, show_verbose, show_system); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9cae8c8..86c3bd3 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -19,6 +19,7 @@ #include "catalog/pg_cast_d.h" #include "catalog/pg_class_d.h" #include "catalog/pg_default_acl_d.h" +#include "catalog/pg_index.h" #include "common/logging.h" #include "fe_utils/mbprint.h" @@ -47,7 +48,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname, const char *pnspname, const char *prsname); static void printACLColumn(PQExpBuffer buf, const char *colname); static bool listOneExtensionContents(const char *extname, const char *oid); - +static bool describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname); /*---------------- * Handlers for various slash commands displaying some sort of list @@ -6361,3 +6366,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern, PQclear(res); return true; } + +/* + * \dip + * Describes index properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexProperties(const char *pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + + static const bool translate_columns[] = {false, false, false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT" + " n.nspname AS \"%s\",\n" + " c.relname AS \"%s\",\n" + " am.amname AS \"%s\",\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Access method")); + appendPQExpBuffer(&buf, + pset.sversion >= 90600 ? + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n" + : + " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("Clusterable"), + gettext_noop("Index scan"), + gettext_noop("Bitmap scan"), + gettext_noop("Backward scan")); + appendPQExpBufferStr(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + "WHERE c.relkind='i'\n" + " AND n.nspname !~ 'pg_toast'\n"); + + if (!showSystem && !pattern) + appendPQExpBufferStr(&buf, + " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.relname", NULL, NULL); + + appendPQExpBufferStr(&buf, "ORDER BY 1;"); + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("Index properties"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + +/* + * \dicp + * Describes index index column properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexColumnProperties(const char *index_pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + int i; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT DISTINCT c.oid,\n" + " n.nspname,\n" + " c.relname,\n" + " am.amname,\n" + " c2.relname\n" + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"); + + appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n"); + + if (!showSystem && !index_pattern) + appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n" + "AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, index_pattern, true, false, + "n.nspname", "c.relname", NULL, + "pg_catalog.pg_table_is_visible(c.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 2, 3;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + if (PQntuples(res) == 0) + { + if (!pset.quiet) + { + if (index_pattern) + pg_log_error("Did not find any index named \"%s\".", + index_pattern); + else + pg_log_error("Did not find any relations."); + } + PQclear(res); + return false; + } + + for (i = 0; i < PQntuples(res); i++) + { + const char *oid = PQgetvalue(res, i, 0); + const char *nspname = PQgetvalue(res, i, 1); + const char *idxname = PQgetvalue(res, i, 2); + const char *amname = PQgetvalue(res, i, 3); + const char *tabname = PQgetvalue(res, i, 4); + + if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname, + tabname)) + { + PQclear(res); + return false; + } + if (cancel_pressed) + { + PQclear(res); + return false; + } + } + + PQclear(res); + return true; +} + +static bool +describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + char *footers[3] = {NULL, NULL}; + static const bool translate_columns[] = {false, false, false, false, false, + false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT\n" + " a.attname AS \"%s\",\n" + " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n" + " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n", + gettext_noop("Column name"), + gettext_noop("Expr"), + gettext_noop("Opclass")); + + if (pset.sversion >= 90600) + appendPQExpBuffer(&buf, + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%3$s\"," + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%4$s\"," + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Returnable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + else + appendPQExpBuffer(&buf, + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%4$s\",\n" + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%6$s\",\n" + " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n" + " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n" + " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n" + " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n", + INDOPTION_DESC, + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + INDOPTION_NULLS_FIRST, + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + + appendPQExpBuffer(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n"); + if (pset.sversion < 90600) + appendPQExpBuffer(&buf, + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"); + appendPQExpBuffer(&buf, + "WHERE c.oid = %s\n" + "ORDER BY a.attnum", + oid); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + if (PQntuples(res) == 0) + { + PQclear(res); + return true; + } + + myopt.nullPrint = NULL; + myopt.title = psprintf(_("Index %s.%s"), nspname, idxname); + footers[0] = psprintf(_("Table: %s"), tabname); + footers[1] = psprintf(_("Access method: %s"), amname); + myopt.footers = footers; + myopt.topt.default_footer = false; + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + PQclear(res); + return true; +} diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 6c87401..f9c3ea6 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -131,4 +131,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter const char *opclass_pattern, bool verbose); +/* \dip */ +extern bool describeIndexProperties(const char *pattern, bool showSystem); + +/* \dicp */ +extern bool describeIndexColumnProperties(const char *indexPattern, + bool showSystem); + #endif /* DESCRIBE_H */ diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index dc591c3..dbb873e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -249,6 +249,8 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n")); fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); + fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n")); + fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5d14a93..0e3a1eff 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1427,8 +1427,9 @@ psql_completion(const char *text, int start, int end) "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", - "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", - "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", + "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip", + "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", + "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding", diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 4d17be3..2580bae 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -4803,3 +4803,19 @@ List operators of family related to access method brin | oid | | oid_minmax_ops | yes (1 row) +-- check printing info about indexes +\dip pg_am_name_index + Index properties + Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan +------------+------------------+---------------+-------------+------------+-------------+--------------- + pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes +(1 row) + +\dicp pg_am_name_index + Index pg_catalog.pg_am_name_index + Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls +-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+-------------- + amname | amname | name_ops | yes | no | yes | no | yes | yes | yes +Table: pg_am +Access method: btree + diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 77941c4..95e7dbe 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1139,3 +1139,6 @@ drop role regress_partitioning_role; \dAp brin uuid_minmax_ops \dAp * pg_catalog.uuid_ops \dAc brin pg*.oid* +-- check printing info about indexes +\dip pg_am_name_index +\dicp pg_am_name_index -- 2.7.4 --------------251D77BCC012992979481C12--