How about a psql backslash command to show GUCs?

hi

st 6. 4. 2022 v 19:49 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

It's not difficult to get psql to show you the current value
of a single GUC --- "SHOW" does that fine, and it has tab
completion support for the GUC name. However, I very often
find myself resorting to the much more tedious

select * from pg_settings where name like '%foo%';

when I want to see some related parameters, or when I'm a bit
fuzzy on the exact name of the parameter. Not only is this
a lot of typing, but unless I'm willing to type even more to
avoid using "*", I'll get a wall of mostly unreadable text,
because pg_settings is far too wide and cluttered with
low-grade information.

In the discussion about adding privileges for GUCs [1], there
was a proposal to add a new psql backslash command to show GUCs,
which could reduce this problem to something like

\dcp *foo*

It can be a good idea. I am not sure about \dcp. maybe \show can be better?

Regards

Pavel

On 2022-Apr-06, Tom Lane wrote:

However, I very often find myself resorting to the much more tedious

select * from pg_settings where name like '%foo%';

when I want to see some related parameters, or when I'm a bit
fuzzy on the exact name of the parameter.

Been there many times, so +1 for the general idea.

In the discussion about adding privileges for GUCs [1], there
was a proposal to add a new psql backslash command to show GUCs,
which could reduce this problem to something like

\dcp *foo*

+1. As for command name, I like \show as proposed by Pavel.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

On Apr 6, 2022, at 10:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

So does anyone else like this idea?

Privileges on targets other than parameters have a \d command to show the privileges, as listed in doc/src/sgml/ddl.sgml. There isn't an obvious reason for omitting parameters from the list so covered.

One of the ideas that got punted in the recent commit was to make it possible to revoke SET on a USERSET guc. For example, it might be nice to REVOKE SET ON PARAMETER work_mem FROM PUBLIC. That can't be done now, but for some select parameters, we might implement that in the future by promoting them to SUSET with a default GRANT SET...TO PUBLIC. When connecting to databases of different postgres versions (albeit only those version 15 and above), it'd be nice to quickly see what context (USERSET vs. SUSET) is assigned to the parameter, and whether the GRANT has been revoked.

So yes, +1 from me.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 4/6/22 13:58, Alvaro Herrera wrote:

On 2022-Apr-06, Tom Lane wrote:

However, I very often find myself resorting to the much more tedious

select * from pg_settings where name like '%foo%';

when I want to see some related parameters, or when I'm a bit
fuzzy on the exact name of the parameter.

Been there many times, so +1 for the general idea.

In the discussion about adding privileges for GUCs [1], there
was a proposal to add a new psql backslash command to show GUCs,
which could reduce this problem to something like

\dcp *foo*

+1. As for command name, I like \show as proposed by Pavel.

+1

I'd love something for the same reasons.

No as sure about \show though. That seems like it could be confused with
showing other stuff. Maybe consistent with \sf[+] and \sv[+] we could
add \sc[+]?

Joe

Joe Conway <mail@joeconway.com> writes:

No as sure about \show though. That seems like it could be confused with
showing other stuff. Maybe consistent with \sf[+] and \sv[+] we could
add \sc[+]?

Hmm ... my first reaction to that was "no, it should be \sp for
'parameter'". But with the neighboring \sf for 'function', it'd
be easy to think that maybe 'p' means 'procedure'.

I do agree that \show might be a bad choice, the reason being that
the adjacent \set command is for psql variables not GUCs; if we
had a \show I'd sort of expect it to be a variant spelling of
"\echo :variable".

"\sc" isn't awful perhaps.

Ah, naming ... the hardest problem in computer science.

regards, tom lane

On Wed, 6 Apr 2022 at 13:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:

when I want to see some related parameters, or when I'm a bit
fuzzy on the exact name of the parameter. Not only is this
a lot of typing, but unless I'm willing to type even more to
avoid using "*", I'll get a wall of mostly unreadable text,
because pg_settings is far too wide and cluttered with
low-grade information.

I may be suffering from some form of the Mandela Effect but I have a
distinct memory that once upon a time SHOW actually took patterns like
\d does. I keep trying it, forgetting that it doesn't actually work.
I'm guessing my brain is generalizing and assuming SHOW fits into the
same pattern as \d commands and just keeps doing it even after I've
learned repeatedly that it doesn't work.

Personally I would like to just make the world match the way my brain
thinks it is and make this just work:

SHOW enable_*

I don't see any value in allowing * or ? in GUC names so I don't even
see a downside to this. It would be way easier to discover than
another \ command

--
greg

On 4/6/22 2:40 PM, Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

No as sure about \show though. That seems like it could be confused with
showing other stuff. Maybe consistent with \sf[+] and \sv[+] we could
add \sc[+]?

Hmm ... my first reaction to that was "no, it should be \sp for
'parameter'". But with the neighboring \sf for 'function', it'd
be easy to think that maybe 'p' means 'procedure'.

I do agree that \show might be a bad choice, the reason being that
the adjacent \set command is for psql variables not GUCs; if we
had a \show I'd sort of expect it to be a variant spelling of
"\echo :variable".

"\sc" isn't awful perhaps.

Ah, naming ... the hardest problem in computer science.

(but the easiest thing to have an opinion on ;)

+1 on the feature proposal.

I am a bit torn between "\dcp" (or \dsetting / \dconfig? we don't
necessarily need for it to be super short) and "\sc". Certainly with
pattern matching the interface for the "\d" commands would fit that
pattern. "\sc" would make sense for a thorough introspection of what is
in the GUC. That said, we get that with SHOW today.

So I'm leaning towards something in the "\d" family.

Thanks,

Jonathan

On Apr 6, 2022, at 2:34 PM, Jonathan S. Katz <jkatz@postgresql.org> wrote:

"\sc" would make sense

I originally wrote the command as \dcp (describe configuration parameter) because \dp (describe parameter) wasn't available. The thing we're showing is a "parameter", not a "config". If we're going to use a single letter, I'd prefer /p/.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

I am a bit torn between "\dcp" (or \dsetting / \dconfig? we don't
necessarily need for it to be super short) and "\sc". Certainly with
pattern matching the interface for the "\d" commands would fit that
pattern. "\sc" would make sense for a thorough introspection of what is
in the GUC. That said, we get that with SHOW today.

So I'm leaning towards something in the "\d" family.

I agree that \d-something makes the most sense from a functionality
standpoint. But I don't want to make the name too long, even if we
do have tab completion to help.

\dconf maybe?

regards, tom lane

On Wed, Apr 6, 2022 at 6:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

I am a bit torn between "\dcp" (or \dsetting / \dconfig? we don't
necessarily need for it to be super short) and "\sc". Certainly with
pattern matching the interface for the "\d" commands would fit that
pattern. "\sc" would make sense for a thorough introspection of what is
in the GUC. That said, we get that with SHOW today.

So I'm leaning towards something in the "\d" family.

I agree that \d-something makes the most sense from a functionality
standpoint. But I don't want to make the name too long, even if we
do have tab completion to help.

\dconf maybe?

I don't have a strong preference, but just tossing it out there; maybe
embrace the novelty of GUC?

\dguc

David J.

On 4/6/22 9:18 PM, David G. Johnston wrote:

On Wed, Apr 6, 2022 at 6:16 PM Tom Lane <tgl@sss.pgh.pa.us

I agree that \d-something makes the most sense from a functionality
standpoint.  But I don't want to make the name too long, even if we
do have tab completion to help.

\dconf maybe?

I don't have a strong preference, but just tossing it out there; maybe
embrace the novelty of GUC?

\dguc

+1 for \dconf

Jonathan

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

+1 for \dconf

Here's a draft patch using \dconf. No tests or docs yet.

regards, tom lane

I also find myself querying pg_settings all too often. More typing
than I'd like.

On Thu, 7 Apr 2022 at 06:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I do agree that \show might be a bad choice, the reason being that
the adjacent \set command is for psql variables not GUCs; if we
had a \show I'd sort of expect it to be a variant spelling of
"\echo :variable".

I also think \show is not a great choice. I'd rather see us follow the
\d pattern for showing information about objects in the database.

"\sc" isn't awful perhaps.

I think \dG is pretty good. G for GUC.

David

On 06.04.2022 20:48, Tom Lane wrote:

However, I very often
find myself resorting to the much more tedious

select * from pg_settings where name like '%foo%';

In the discussion about adding privileges for GUCs [1], there
was a proposal to add a new psql backslash command to show GUCs,
which could reduce this problem to something like

\dcp *foo*

+1, great idea.

Right now I use the psql :show variable in my .psqlrc for this purpose:

=# \echo :show
SELECT name, current_setting(name) AS value, context FROM pg_settings\g
(format=wrapped columns=100) | grep

=# :show autovacuum
 autovacuum                             |
on                                    | sighup
 autovacuum_analyze_scale_factor        |
0.1                                   | sighup
 autovacuum_analyze_threshold           |
50                                    | sighup
 autovacuum_freeze_max_age              |
200000000                             | postmaster
 autovacuum_max_workers                 |
3                                     | postmaster
 autovacuum_multixact_freeze_max_age    |
400000000                             | postmaster
 autovacuum_naptime                     |
1min                                  | sighup
 autovacuum_vacuum_cost_delay           |
2ms                                   | sighup
 autovacuum_vacuum_cost_limit           |
-1                                    | sighup
 autovacuum_vacuum_scale_factor         |
0.2                                   | sighup
 autovacuum_vacuum_threshold            |
50                                    | sighup
 autovacuum_work_mem                    |
-1                                    | sighup
 log_autovacuum_min_duration            |
-1                                    | sighup

As for the name, I can't think of a better candidate. Any of the
previously suggested list of \dconf, \dguc, \dG, \dcp is fine.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

On 4/6/22 23:02, Tom Lane wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

+1 for \dconf

Here's a draft patch using \dconf. No tests or docs yet.

WFM -- using some form of \d<something> makes more sense than
\s<something>, and I can't think of anything better that \dconf.

I will say that I care about context far more often than unit or type
though, so from my point of view I would switch them around with respect
to which is only shown with verbose.

Joe

On 4/6/22 23:25, David Rowley wrote:

I also find myself querying pg_settings all too often. More typing
than I'd like.

On Thu, 7 Apr 2022 at 06:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I do agree that \show might be a bad choice, the reason being that
the adjacent \set command is for psql variables not GUCs; if we
had a \show I'd sort of expect it to be a variant spelling of
"\echo :variable".

I also think \show is not a great choice. I'd rather see us follow the
\d pattern for showing information about objects in the database.

"\sc" isn't awful perhaps.

I think \dG is pretty good. G for GUC.

-1 on anything that is based on "GUC", an ancient and now largely
irrelevant acronym. How many developers, let alone users, know what it
stands for?

\dconf seems fine to me

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On 4/7/22 8:36 AM, Joe Conway wrote:

On 4/6/22 23:02, Tom Lane wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

+1 for \dconf

Here's a draft patch using \dconf.  No tests or docs yet.

WFM -- using some form of \d<something> makes more sense than
\s<something>, and I can't think of anything better that \dconf.

I will say that I care about context far more often than unit or type
though, so from my point of view I would switch them around with respect
to which is only shown with verbose.

I disagree somewhat -- I agree the context should be in the regular
view, but unit and type are also important. If I had to choose to drop
one, I'd choose type as it could be inferred, but I would say better to
keep them all.

The downside is that by including context, the standard list appears to
push past my 99px width terminal in non-enhanced view, but that may be OK.

A couple of minor things:

+ appendPQExpBufferStr(&buf, "ORDER BY 1;");

I don't know how much we do positional ordering in our queries, but it
may be better to explicitly order by "s.name". I doubt this column name
is likely to change, and if for some reason someone shuffles the output
order of \dconf, it makes it less likely to break someone's view.

I did not test this via an extension, but we do allow for mixed case in
custom GUCs:

postgres=# SHOW jkatz.test;
JKATZ.test
------------
abc

I don't know if we want to throw a "LOWER(s.name)" on at least the
ordering, given we allow for "SHOW" itself to load these case-insensitively.

+ fprintf(output, _(" \\dconf[+] [PATTERN] list configuration
parameters\n"));

Maybe to appeal to all crowds, we say "list configuration parameters
(GUCs)"?

Thanks,

Jonathan

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

On 4/7/22 8:36 AM, Joe Conway wrote:

I will say that I care about context far more often than unit or type
though, so from my point of view I would switch them around with respect
to which is only shown with verbose.

I disagree somewhat -- I agree the context should be in the regular
view, but unit and type are also important. If I had to choose to drop
one, I'd choose type as it could be inferred, but I would say better to
keep them all.

Given the new ability to grant privileges on GUCs, context alone is not
sufficient to know when something can be set. So the context and the
privileges seem like they should go together, and that's why I have them
both under "+".

I can see the argument for relegating type to the "+" format, in hopes of
keeping the default display narrow enough for ordinary terminal windows.

A couple of minor things:
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
I don't know how much we do positional ordering in our queries, but it
may be better to explicitly order by "s.name".

"ORDER BY n" seems to be the de facto standard in describe.c. Perhaps
there's an argument for changing it throughout that file, but I don't
think this one function should be out of step with the rest.

I don't know if we want to throw a "LOWER(s.name)" on at least the
ordering, given we allow for "SHOW" itself to load these case-insensitively.

Yeah, I went back and forth on that myself --- I was looking at the
example of DateStyle, and noticing that although you see it in mixed
case in the command's output, tab completion isn't happy unless you
enter it in lower case (ie, date<TAB> works, Date<TAB> not so much).
Forcibly lowercasing the command output would fix that inconsistency,
but on the other hand it introduces an inconsistency with what the
pg_settings view shows. Not sure what's the least bad. We might be
able to fix the tab completion behavior, if we don't mind complicating
tab-complete.c even more; so probably that's the thing to look at
before changing the output.

Maybe to appeal to all crowds, we say "list configuration parameters
(GUCs)"?

I'm in the camp that says that GUC is not an acronym we wish to expose
to end users.

regards, tom lane

On Thu, Apr 7, 2022 at 7:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

Maybe to appeal to all crowds, we say "list configuration parameters

(GUCs)"?

I'm in the camp that says that GUC is not an acronym we wish to expose
to end users.

I am too. In any case, either go all-in with GUC (i.e., \dG or \dguc) or
pretend it doesn't exist - an in-between position is unappealing.

David J.