From a89d18fdbee65ec9ee6bc7d5bd98190a21512386 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 18 Dec 2021 14:58:06 -0600 Subject: [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose (??) The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index fae5940b54e..14c772c18f3 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -370,6 +370,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -757,6 +758,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -764,7 +766,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -789,7 +794,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -815,7 +820,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -869,7 +874,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1952,14 +1957,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index f67bf0b8925..1d18f7c6cd9 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -138,12 +138,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -175,6 +175,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -212,7 +217,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -232,12 +237,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -908,7 +919,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -959,20 +970,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -5023,7 +5038,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -5045,6 +5060,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 273f974538e..10271ba1393 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.42.0