Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ncUu4-0001Ak-RD for pgsql-hackers@arkaria.postgresql.org; Thu, 07 Apr 2022 16:22:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ncUu3-0002Tv-Lg for pgsql-hackers@arkaria.postgresql.org; Thu, 07 Apr 2022 16:22:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ncUu3-0002Tm-8X for pgsql-hackers@lists.postgresql.org; Thu, 07 Apr 2022 16:22:35 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ncUtw-0000G2-7V for pgsql-hackers@lists.postgresql.org; Thu, 07 Apr 2022 16:22:34 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 237GMMIs3369131; Thu, 7 Apr 2022 12:22:22 -0400 From: Tom Lane To: Justin Pryzby cc: "Jonathan S. Katz" , "David G. Johnston" , Joe Conway , Alvaro Herrera , pgsql-hackers@lists.postgresql.org, Pavel Luzanov , Greg Stark , Mark Dilger Subject: Re: How about a psql backslash command to show GUCs? In-reply-to: <20220407152449.GD24419@telsasoft.com> References: <202204061758.leffczewl4kd@alvherre.pgsql> <3123259.1649270440@sss.pgh.pa.us> <38891fd8-dceb-c956-ec5c-61c1a3e9486d@postgresql.org> <3201870.1649294165@sss.pgh.pa.us> <6693b128-b9e5-fd5e-e8d2-b331dac52cb8@postgresql.org> <3221981.1649300574@sss.pgh.pa.us> <20220407152449.GD24419@telsasoft.com> Comments: In-reply-to Justin Pryzby message dated "Thu, 07 Apr 2022 10:24:49 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3369077.1649348495.0@sss.pgh.pa.us> Date: Thu, 07 Apr 2022 12:22:22 -0400 Message-ID: <3369130.1649348542@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3369077.1649348495.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Justin Pryzby writes: > SHOW and current_setting() translate to human units, which is particular= ly > useful for some settings, like those with units of 8k pages. = > Is it better to use that "cooked" version for display in the backslash c= ommand > instead of the raw view from pg_settings ? Oh, that's a good idea --- lets us drop the units column entirely. The attached revision does that and moves the "type" column to secondary status, as discussed upthread. I also added docs and simple regression tests, and fixed two problems that were preventing completion of custom (qualified) GUC names (we need to use the VERBATIM option for those queries). There remains the issue that tab completion for GUC names ought to be case-insensitive, but that's a pre-existing bug in tab-complete.c's other GUC name completions too; I'll tackle it later. As for the name, \dconf has a slight plurality in votes so far, so I'm sticking with that. I think this is ready to go unless someone has a significantly better idea. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="add-backslash-dconf-command-1.patch"; charset="us-ascii" Content-ID: <3369077.1649348495.2@sss.pgh.pa.us> Content-Description: add-backslash-dconf-command-1.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 492a960247..4dc6628add 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2205,7 +2205,7 @@ REVOKE ALL ON accounts FROM PUBLIC; PARAMETER sA none - none + \dconf+ SCHEMA diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sg= ml index f01adb1fd2..864be97808 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1380,6 +1380,23 @@ testdb=3D> = = + + \dconf[+] [ pattern ] + + + Lists server configuration parameters and their values. + If pattern + is specified, only parameters whose names match the pattern are + listed. + If + is appended to the command name, each + parameter is listed with its data type, context in which the + parameter can be set, and access privileges (if non-default acces= s + privileges have been granted). + + + + + \dC[+] [ pattern ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 079f4a1a76..bbf4a5a44e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -780,7 +780,14 @@ exec_command_d(PsqlScanState scan_state, bool active_= branch, const char *cmd) success =3D describeTablespaces(pattern, show_verbose); break; case 'c': - success =3D listConversions(pattern, show_verbose, show_system); + if (strncmp(cmd, "dconf", 5) =3D=3D 0) + success =3D describeConfigurationParameters(pattern, + show_verbose, + show_system); + else + success =3D listConversions(pattern, + show_verbose, + show_system); break; case 'C': success =3D listCasts(pattern, show_verbose); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 73bbbe2eb4..47590a1302 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -4434,6 +4434,68 @@ listConversions(const char *pattern, bool verbose, = bool showSystem) return true; } = +/* + * \dconf + * + * Describes configuration parameters. + */ +bool +describeConfigurationParameters(const char *pattern, bool verbose, + bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt =3D pset.popt; + + initPQExpBuffer(&buf); + printfPQExpBuffer(&buf, + "SELECT s.name AS \"%s\", " + "pg_catalog.current_setting(s.name) AS \"%s\"", + gettext_noop("Parameter"), + gettext_noop("Value")); + + if (verbose) + { + appendPQExpBuffer(&buf, + ", s.vartype AS \"%s\", s.context AS \"%s\", ", + gettext_noop("Type"), + gettext_noop("Context")); + if (pset.sversion >=3D 150000) + printACLColumn(&buf, "p.paracl"); + else + appendPQExpBuffer(&buf, "NULL AS \"%s\"", + gettext_noop("Access privileges")); + } + + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_settings s\n"); + + if (verbose && pset.sversion >=3D 150000) + appendPQExpBufferStr(&buf, + " LEFT JOIN pg_catalog.pg_parameter_acl p\n" + " ON pg_catalog.lower(s.name) =3D p.parname\n"); + + processSQLNamePattern(pset.db, &buf, pattern, + false, false, + NULL, "pg_catalog.lower(s.name)", NULL, + NULL); + + appendPQExpBufferStr(&buf, "ORDER BY 1;"); + + res =3D PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint =3D NULL; + myopt.title =3D _("List of configuration parameters"); + myopt.translate_header =3D true; + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + /* * \dy * diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index fd6079679c..4eb2710e27 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -76,6 +76,10 @@ extern bool listDomains(const char *pattern, bool verbo= se, bool showSystem); /* \dc */ extern bool listConversions(const char *pattern, bool verbose, bool showS= ystem); = +/* \dconf */ +extern bool describeConfigurationParameters(const char *pattern, bool ver= bose, + bool showSystem); + /* \dC */ extern bool listCasts(const char *pattern, bool verbose); = diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index b3971bce64..eff9d1a16e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -166,7 +166,7 @@ slashUsage(unsigned short int pager) * Use "psql --help=3Dcommands | wc" to count correctly. It's okay to c= ount * the USE_READLINE line even in builds without that. */ - output =3D PageOutput(137, pager ? &(pset.popt.topt) : NULL); + output =3D PageOutput(138, pager ? &(pset.popt.topt) : NULL); = fprintf(output, _("General\n")); fprintf(output, _(" \\copyright show PostgreSQL usage and d= istribution terms\n")); @@ -231,6 +231,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dAp[+] [AMPTRN [OPFPTRN]] list support function= s of operator families\n")); fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n")); fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n")); + fprintf(output, _(" \\dconf[+] [PATTERN] list configuration paramete= rs\n")); fprintf(output, _(" \\dC[+] [PATTERN] list casts\n")); fprintf(output, _(" \\dd[S] [PATTERN] show object descriptions no= t displayed elsewhere\n")); fprintf(output, _(" \\dD[S+] [PATTERN] list domains\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 025d3f71a1..f47c5acd50 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1690,7 +1690,7 @@ psql_completion(const char *text, int start, int end= ) "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright", "\\crosstabview", "\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp", - "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", + "\\db", "\\dc", "\\dconf", "\\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", @@ -3790,7 +3790,7 @@ psql_completion(const char *text, int start, int end= ) TailMatches("GRANT|REVOKE", MatchAny, MatchAny, "ON", "PARAMETER") || TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny, "ON", "PARA= METER") || TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny, MatchAny, "= ON", "PARAMETER")) - COMPLETE_WITH_QUERY(Query_for_list_of_alter_system_set_vars); + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_alter_system_set_vars); = else if (TailMatches("GRANT", MatchAny, "ON", "PARAMETER", MatchAny) || TailMatches("GRANT", MatchAny, MatchAny, "ON", "PARAMETER", MatchAny)= ) @@ -4542,6 +4542,8 @@ psql_completion(const char *text, int start, int end= ) COMPLETE_WITH_QUERY(Query_for_list_of_access_methods); else if (TailMatchesCS("\\db*")) COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); + else if (TailMatchesCS("\\dconf*")) + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_show_vars); else if (TailMatchesCS("\\dD*")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains); else if (TailMatchesCS("\\des*")) diff --git a/src/fe_utils/string_utils.c b/src/fe_utils/string_utils.c index bca50ec6de..1c61840462 100644 --- a/src/fe_utils/string_utils.c +++ b/src/fe_utils/string_utils.c @@ -918,8 +918,12 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, = const char *pattern, * Convert shell-style 'pattern' into the regular expression(s) we want = to * execute. Quoting/escaping into SQL literal format will be done below * using appendStringLiteralConn(). + * + * If the caller provided a schemavar, we want to split the pattern on + * ".", otherwise not. */ - patternToSQLRegex(PQclientEncoding(conn), NULL, &schemabuf, &namebuf, + patternToSQLRegex(PQclientEncoding(conn), NULL, + (schemavar ? &schemabuf : NULL), &namebuf, pattern, force_escape); = /* diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expecte= d/psql.out index 185c505312..79bdf78971 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -5090,6 +5090,22 @@ List of access methods hash | uuid_ops | uuid | uuid |= 2 | uuid_hash_extended (5 rows) = +-- check \dconf +\dconf enable_seq* +List of configuration parameters + Parameter | Value = +----------------+------- + enable_seqscan | on +(1 row) + +\dconf+ enable_index* + List of configuration parameters + Parameter | Value | Type | Context | Access privileges = +----------------------+-------+------+---------+------------------- + enable_indexonlyscan | on | bool | user | = + enable_indexscan | on | bool | user | = +(2 rows) + -- check \df, \do with argument specifications \df *sqrt List of functions diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 8f49a5f347..0ea00cf8db 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1241,6 +1241,10 @@ drop role regress_partitioning_role; \dAp+ btree float_ops \dAp * pg_catalog.uuid_ops = +-- check \dconf +\dconf enable_seq* +\dconf+ enable_index* + -- check \df, \do with argument specifications \df *sqrt \df *sqrt num* ------- =_aaaaaaaaaa0--