Re: Proposal: knowing detail of config files via SQL

Sawada Masahiko <sawada.mshk@gmail.com> writes:
> As per discussion
> <http://www.postgresql.org/message-id/CAD21AoDkds8Oqbr199wwrCp7fiDvOw6bbb+CGdwQHUF+gX_bVg@mail.gmail.com>,
> I would like to proposal new view like pg_file_settings to know detail
> of config file via SQL.

> - Background
> In 9.4 postgresql.auto.conf is added to support ALTER SYSTEM command
> and that config file is loaded after whenever postgresql.conf is
> loaded.
> That is, postgresql.auto.conf is quite high priority so that the value
> in postgresql.conf can not work at all if DBA set it manually.
> ALTER SYSTEM RESET command can remove the unnecessary value in
> postgresql.auto.conf but  there are no way to know about where the
> value has came from.
> (They can only give the information about the setting in last file it
> is present.)

> - Solution
> The patch not is implemented yet, just proposing now.
> I'm imaging that we can have new pg_file_settings view has following
> column to store current assigned value in config file.
>  - guc value name
>  - guc value
>  - config file path (e.g. /opt/data/postgresql.sql,
> /opt/data/postgresql.auto.conf, /opt/hoge.conf)
> This view could be convenient for DBA to decide if the
> postgresql.auto.conf is useful or not and if it's not useful then DBA
> could use ALTER SYSTEM .. RESET command to remove the same from
> postgresql.auto.conf.

> Also other idea is to add additional columns existing view
> (pg_settings), according to prev discussion.

> Please give me comments.

I still don't understand what problem you think needs to be solved.
It's already perfectly clear from the pg_settings view when a value
came from postgresql.auto.conf.  For instance:


regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';  name   |
setting  |       source       |                   sourcefile                    | sourceline 
 
----------+------------+--------------------+-------------------------------------------------+------------TimeZone |
US/Eastern| configuration file | /home/postgres/testversion/data/postgresql.conf |        531
 
(1 row)

regression=# alter system set timezone = 'Asia/Shanghai';
ALTER SYSTEM
regression=# select pg_reload_conf();pg_reload_conf 
----------------t
(1 row)

regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';  name   |
setting   |       source       |                      sourcefile                      | sourceline 
 

----------+---------------+--------------------+------------------------------------------------------+------------TimeZone
|Asia/Shanghai | configuration file | /home/postgres/testversion/data/postgresql.auto.conf |          3
 
(1 row)

regression=# alter system reset timezone;
ALTER SYSTEM
regression=# select pg_reload_conf();pg_reload_conf 
----------------t
(1 row)

regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';  name   |
setting  |       source       |                   sourcefile                    | sourceline 
 
----------+------------+--------------------+-------------------------------------------------+------------TimeZone |
US/Eastern| configuration file | /home/postgres/testversion/data/postgresql.conf |        531
 
(1 row)


What else is needed?
        regards, tom lane