Re: How about a psql backslash command to show GUCs? - Mailing list pgsql-hackers

Justin Pryzby <pryzby@telsasoft.com> writes:
> SHOW and current_setting() translate to human units, which is particularly
> useful for some settings, like those with units of 8k pages.
> Is it better to use that "cooked" version for display in the backslash command
> 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

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;
       <entry><literal>PARAMETER</literal></entry>
       <entry><literal>sA</literal></entry>
       <entry>none</entry>
-      <entry>none</entry>
+      <entry><literal>\dconf+</literal></entry>
      </row>
      <row>
       <entry><literal>SCHEMA</literal></entry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
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=>
       </varlistentry>


+      <varlistentry>
+        <term><literal>\dconf[+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>]</literal></term> 
+        <listitem>
+        <para>
+        Lists server configuration parameters and their values.
+        If <replaceable class="parameter">pattern</replaceable>
+        is specified, only parameters whose names match the pattern are
+        listed.
+        If <literal>+</literal> 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 access
+        privileges have been granted).
+        </para>
+        </listitem>
+      </varlistentry>
+
+
       <varlistentry>
         <term><literal>\dC[+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>]</literal></term> 
         <listitem>
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 = describeTablespaces(pattern, show_verbose);
                 break;
             case 'c':
-                success = listConversions(pattern, show_verbose, show_system);
+                if (strncmp(cmd, "dconf", 5) == 0)
+                    success = describeConfigurationParameters(pattern,
+                                                              show_verbose,
+                                                              show_system);
+                else
+                    success = listConversions(pattern,
+                                              show_verbose,
+                                              show_system);
                 break;
             case 'C':
                 success = 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 = 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 >= 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 >= 150000)
+        appendPQExpBufferStr(&buf,
+                             "  LEFT JOIN pg_catalog.pg_parameter_acl p\n"
+                             "  ON pg_catalog.lower(s.name) = p.parname\n");
+
+    processSQLNamePattern(pset.db, &buf, pattern,
+                          false, false,
+                          NULL, "pg_catalog.lower(s.name)", NULL,
+                          NULL);
+
+    appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+    res = PSQLexec(buf.data);
+    termPQExpBuffer(&buf);
+    if (!res)
+        return false;
+
+    myopt.nullPrint = NULL;
+    myopt.title = _("List of configuration parameters");
+    myopt.translate_header = 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 verbose, bool showSystem);
 /* \dc */
 extern bool listConversions(const char *pattern, bool verbose, bool showSystem);

+/* \dconf */
+extern bool describeConfigurationParameters(const char *pattern, bool verbose,
+                                            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=commands | wc" to count correctly.  It's okay to count
      * the USE_READLINE line even in builds without that.
      */
-    output = PageOutput(137, pager ? &(pset.popt.topt) : NULL);
+    output = PageOutput(138, pager ? &(pset.popt.topt) : NULL);

     fprintf(output, _("General\n"));
     fprintf(output, _("  \\copyright             show PostgreSQL usage and distribution terms\n"));
@@ -231,6 +231,7 @@ slashUsage(unsigned short int pager)
     fprintf(output, _("  \\dAp[+] [AMPTRN [OPFPTRN]]   list support functions 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 parameters\n"));
     fprintf(output, _("  \\dC[+]  [PATTERN]      list casts\n"));
     fprintf(output, _("  \\dd[S]  [PATTERN]      show object descriptions not 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", "PARAMETER") ||
              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/expected/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*