Re: Merging postgresql.conf and postgresql.auto.conf

On 1/16/15 10:32 PM, David G Johnston wrote:
>>>>> One thought I have in this line is that currently there doesn't seem
>>>> >>>to
>>>>> >>> >be
>>>>> >>> >a way to know if the setting has an entry both in postgresql.conf and
>>>>> >>> >postgresql.auto.conf, if we can have some way of knowing the same
>>>>> >>> >(pg_settings?), then it could be convenient for user to decide if the
>>>>> >>> >value
>>>>> >>> >in postgresql.auto.conf is useful or not and if it's not useful then
>>>> >>>use
>>>>> >>> >Alter System .. Reset command to remove the same from
>>>>> >>> >postgresql.auto.conf.
>>>> >>>
>>>> >>>I think one way is that pg_settings has file name of variables, But
>>>> >>>It would not affect to currently status of postgresql.conf
>>>> >>>So we would need to parse postgresql.conf again at that time.
>>>> >>>
>>> >>
>>> >>Yeah that could be a possibility, but I think that will break the
>>> >>existing
>>> >>command('s) as this is the common infrastructure used for SHOW ..
>>> >>commands as well which displays the guc value that is used by
>>> >>current session rather than the value in postgresql.conf.
>> >
>> >You're right.
>> >pg_setting and SHOW command use value in current session rather than
>> >config file.
>> >It might break these common infrastructure.
> Two changes solve this problem in what seems to be a clean way.
> 1) Upon each parsing of postgresql.conf we store all assigned variables
> somewhere

Parsing is relatively cheap, and it's not like we need high performance from this. So, -1 on permanent storage.

> 2) We display these assignments in a new pg_settings column named
> "system_reset_val"
>
> I would also extend this to include:
> a) upon each parsing of postgresql.auto.conf we store all assigned variables
> somewhere (maybe the same place as postgresql.conf and simply label the file
> source)

You can not assume there are only postgresql.conf and postgresql.auto.conf. Complex environments will have multiple included files.

> b) add an "alter_system_val" field to show that value (or null)
> c) add a "db_role_val" to show the current value for the session via
> pg_db_role_setting

You're forgetting that there are also per-role settings. And I'm with Robert; what's wrong with sourcefile and sourceline? Perhaps we just need to teach those about ALTER ROLE SET and ALTER DATABASE SET (if they don't already know about them).

> c.1) add a "db_role_id" to show the named user that is being used for the
> db_role_val lookup
>
> The thinking for c.1 is that in situations with role hierarchies and SET
> ROLE usage it would be nice to be able to query what the connection role -
> the one used during variable lookup - is.

I'm losing track of exactly what we're trying to solve here, but...

If the goal is to figure out what settings would be in place for a specific user connecting to a specific database, then we should create a SRF that does just that (accepting a database name and role name). You could then do...

SELECT * FROM pg_show_all_settings( 'database', 'role' ) a;

> I'm probably going overkill on this but there are not a lot of difference
> sources nor do they change frequently so extending the pg_settings view to
> be more of a one-stop-shopping for this information seems to make sense.

Speaking of overkill... one thing that you currently can't do is find out what #includes have been processed. Perhaps it's worth having a SRF that would return that info...

> As it relates back to this thread the desired "merging" ends up being done
> inside this view and at least gives the DBA a central location (well, along
> with pg_db_role_setting) to go and look at the configuration landscape for
> the system.

I think the goal is good, but the interface needs to be rethought.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com