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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
