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

On Tue, Jan 20, 2015 at 1:24 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 1/16/15 10:32 PM, David G Johnston wrote:

> 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.

​OK
​

>
>
> 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.
>

​#include files still appear to come from postgresql.conf; I'm not
proposing we try and memorize every single instance of a variable
declaration and provide a global "overlaps" query - though that piece
already seems to be in place...​

> 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).
>

​Actually, there are not separate PER ROLE and PER DATABASE settings even
though there are different SQL commands. The catalog is simply
"pg_db_role_setting" with the use of "all" tags (i.e., 0) as necessary.
But I see where you are going and do not disagree that precedence
information could be useful.

sourceline and sourcefile ​pertain only to the current value while the
point of adding these other pieces is to provide a snapshot of all the
different mappings that the system knows about; instead of having to tell a
user to go look in two different files (and associated includes) and a
database catalog to find out what possible values are in place. That
doesn't solve the need to scan the catalog to see other possible values -
though you could at least put a counter in pg_settings that indicates how
many pg_db_role_setting entries reference the given variable so that if
non-zero the user is clued into the fact that they need to check out said
catalog table.

>
> 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;
>

​This is fine - but I'm thinking about situations where a user immediately
SET ROLE on their session and typically operate as said user; if they try
doing an ALTER ROLE SET ​for this SET ROLE user it will not work because
their login user is what matters. This is probably a solution looking for
a problem but it is a dynamic that one needs to be aware of.

>
> 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
>

​My main concern with the UI would be too-much-information; but otherwise
if we accept the premise that we should include as much as possible and
then let the user (or us) provide more useful subsets based upon common
use-cases the main issue is making sure we've identified all of the
relevant information that needs to be captured - even if it is something as
minor as the whole logon vs. current role dynamic. I'm ignoring the
cost/benefit aspect of implementation for the moment largely because I do
known enough about the backend to make reasonable comments. But much of
the data is already present in various views and catalogs - I just think
having one-stop-shop would be useful and go a long way toward addressing
concerns like: "where is that stupid setting coming from" ​or "what will
happen if I make this change?"

David J.