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

Sawada Masahiko wrote
> On Fri, Jan 16, 2015 at 12:54 PM, Amit Kapila <

> amit.kapila16@

> > wrote:
>> On Thu, Jan 15, 2015 at 9:48 PM, Sawada Masahiko <

> sawada.mshk@

> >
>> wrote:
>>> On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila <

> amit.kapila16@

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

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.

David J.





--
View this message in context:
http://postgresql.nabble.com/Merging-postgresql-conf-and-postgresql-auto-conf-tp5833910p5834386.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.