Re: Proposal: knowing detail of config files via SQL

On 1/22/15 11:13 AM, Sawada Masahiko wrote:
> Hi,
>
> 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.

Would this view have a row for every option in a config file? IE: if you set something in both postgresql.conf and postgresql.auto.conf, would it show up twice? I think it should, and that there should be a way to see which setting is actually in effect.

It looks like you're attempting to handle #include, yes?

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

I think it would be useful to have a separate view that shows all occurrences of a setting. I recall some comment about source_file and source_line not always being correct in pg_settings; if that's true we should fix that.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com