Re: Proposal: knowing detail of config files via SQL

Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)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