From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Merging postgresql.conf and postgresql.auto.conf |
Date: | 2015-01-20 20:24:00 |
Message-ID: | 54BEB960.6000609@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-01-20 20:32:54 | Re: proposal: searching in array function - array_position |
Previous Message | Robert Haas | 2015-01-20 20:20:36 | Re: pgaudit - an auditing extension for PostgreSQL |