Re: Shouldn't pg_settings.enumvals be array of text?

On Mon, 6 Oct 2008, Magnus Hagander wrote:

> Alvaro Herrera wrote:
>> Let's have it as text[] and have psql apply array_to_string() over it.
>
> Eh, how can psql do this? You access the view with a simple SELECT *
> FROM pg_settings, no?

The pg_settings view is a thin wrapper over what comes out of
show_all_settings. Right now, to generate the enumvals field that does a
call to config_enum_get_options to manually build a string out of the
data. That's a pretty low-level chunk of code, basically doing

s+=entry[record]+", "

for each non-hidden entry to build the string.

When I was looking at this code for the first time recently I thought the
same thing Tom did here--that this was kind of odd and it should give a
text array back instead. I would even volunteer to take a stab at writing
that change myself just to get some more practice with this section of
code, should be able to squeeze that in over the next month.

The fact that pg_settings was already way too wide even before the recent
batch of additions is kind of a separate issue, and I don't think that
doing some special case for the enumvals will be sufficient to make a dent
in that problem.

What I wanted to do once the additions settled down (which means next as
of today) was update the documentation to suggest some neat ways you might
use the information in pg_settings, like those I've been including as
examples in recent messages. The queries there that were agreed to be
useful might even turn into official subset views. Here's some examples
of interesting subsets that do fit in my terminal session and what they
might look like in view form:

CREATE VIEW pg_settings_source AS
SELECT name,setting,source,sourcefile,sourceline FROM pg_settings;

CREATE VIEW pg_settings_modified AS
SELECT name,current_setting(name),unit,setting,boot_val
FROM pg_settings WHERE NOT setting=boot_val;

CREATE VIEW pg_settings_session_changes AS
SELECT name,setting,unit,reset_val
FROM pg_settings WHERE NOT setting=reset_val;

I'm torn on whether putting them into predefined views is really a good
idea for a lot of reasons, including the fact that just putting them into
the documentation (rather than in system_views.sql) helps popularize the
idea that pg_settings is a useful interface admins can use to help manage
their configurations. That's a helpful mindset for both the "lots of
installs to manage" and "can only reach the hosted server on port 5432"
crowds.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD