Re: Proposal: knowing detail of config files via SQL

On Tue, Jan 27, 2015 at 3:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> David Johnston <david.g.johnston@gmail.com> writes:
>>> On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Is that a requirement, and if so why?  Because this proposal doesn't
>>>> guarantee any such knowledge AFAICS.
>>
>>> The proposal provides for SQL access to all possible sources of variable
>>> value setting and, ideally, a means of ordering them in priority order, so
>>> that a search for TimeZone would return two records, one for
>>> postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
>>> 2 respectively - so that in looking at that result if the
>>> postgresql.auto.conf entry were to be removed the user would know that what
>>> the value is in postgresql.conf that would become active.  Furthermore, if
>>> postgresql.conf has a setting AND there is a mapping in an #included file
>>> that information would be accessible via SQL as well.
>>
>> I know what the proposal is.  What I am questioning is the use-case that
>> justifies having us build and support all this extra mechanism.  How often
>> does anyone need to know what the "next down" variable value would be?
>
> I believe I've run into situations where this would be useful.  I
> wouldn't be willing to put in the effort to do this myself, but I
> wouldn't be prepared to vote against a high-quality patch that someone
> else felt motivated to write.
>

Attached patch adds new view pg_file_settings (WIP version).
This view behaves like followings,
[postgres][5432](1)=# select * from pg_file_settings ;
            name            |      setting       |
  sourcefile
----------------------------+--------------------+--------------------------------------------------------
 max_connections            | 100                |
/home/masahiko/pgsql/master/3data/postgresql.conf
 shared_buffers             | 128MB              |
/home/masahiko/pgsql/master/3data/postgresql.conf
 dynamic_shared_memory_type | posix              |
/home/masahiko/pgsql/master/3data/postgresql.conf
 log_timezone               | Japan              |
/home/masahiko/pgsql/master/3data/postgresql.conf
 datestyle                  | iso, mdy           |
/home/masahiko/pgsql/master/3data/postgresql.conf
 timezone                   | Japan              |
/home/masahiko/pgsql/master/3data/postgresql.conf
 lc_messages                | C                  |
/home/masahiko/pgsql/master/3data/postgresql.conf
 lc_monetary                | C                  |
/home/masahiko/pgsql/master/3data/postgresql.conf
 lc_numeric                 | C                  |
/home/masahiko/pgsql/master/3data/postgresql.conf
 lc_time                    | C                  |
/home/masahiko/pgsql/master/3data/postgresql.conf
 default_text_search_config | pg_catalog.english |
/home/masahiko/pgsql/master/3data/postgresql.conf
 work_mem                   | 128MB              |
/home/masahiko/pgsql/master/3data/hoge.conf
 checkpoint_segments        | 300                |
/home/masahiko/pgsql/master/3data/hoge.conf
 enable_indexscan           | off                |
/home/masahiko/pgsql/master/3data/postgresql.auto.conf
 work_mem                   | 64MB               |
/home/masahiko/pgsql/master/3data/postgresql.auto.conf

[postgres][5432](1)=# select name, setting from pg_settings where name
= 'work_mem';
   name   | setting
----------+---------
 work_mem | 65536
(1 row)

Above query result shows that both hoge.conf and postgresql.auto.conf
have same config parameter work_mem, and work_mem in auto.conf is
effecitve.
We can confirm these parameter to decide if the postgresql.auto.conf
is useful or not.

Regards,

-------
Sawada Masahiko