Thread: Merging postgresql.conf and postgresql.auto.conf

Hi all,

The postgresql.auto.conf is loaded after loading of postgresql.conf
whenever configuration file is loaded or reloaded.
This means that parameter in postgresql.auto.conf is quite high
priority, so the parameter in postgresql.conf does not work at all
even if user set it manually.

If user want to change stopped postgresql server then user need to
merge two configuration file(postgresql.conf and postgresql.auto.conf)
while maintaining the consistency manually.

From an operational perspective having a written config with duplicate
entries is not good thing.
I think we need to merge two configuration file into one (or more than
one, if it uses like 'include' word)

The one solution is to add merging tool/commnand which merges two
configuration file while maintaining the consistency.
This topic have been already discussed?

Regards,

-------
Sawada Masahiko

Sawada Masahiko <sawada.mshk@gmail.com> writes:
> The postgresql.auto.conf is loaded after loading of postgresql.conf
> whenever configuration file is loaded or reloaded.
> This means that parameter in postgresql.auto.conf is quite high
> priority, so the parameter in postgresql.conf does not work at all
> even if user set it manually.

> If user want to change stopped postgresql server then user need to
> merge two configuration file(postgresql.conf and postgresql.auto.conf)
> while maintaining the consistency manually.

>> From an operational perspective having a written config with duplicate
> entries is not good thing.
> I think we need to merge two configuration file into one (or more than
> one, if it uses like 'include' word)

> The one solution is to add merging tool/commnand which merges two
> configuration file while maintaining the consistency.
> This topic have been already discussed?

Yes.  The entire reason that postgresql.auto.conf is separate is that
we despaired of reading and rewriting postgresql.conf automatically
without making a hash of material in the comments.  Calling the logic
a "merge tool" does not make that problem go away.
        regards, tom lane

On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sawada Masahiko <sawada.mshk@gmail.com> writes:
>> The postgresql.auto.conf is loaded after loading of postgresql.conf
>> whenever configuration file is loaded or reloaded.
>> This means that parameter in postgresql.auto.conf is quite high
>> priority, so the parameter in postgresql.conf does not work at all
>> even if user set it manually.
>
>> If user want to change stopped postgresql server then user need to
>> merge two configuration file(postgresql.conf and postgresql.auto.conf)
>> while maintaining the consistency manually.
>
>>> From an operational perspective having a written config with duplicate
>> entries is not good thing.
>> I think we need to merge two configuration file into one (or more than
>> one, if it uses like 'include' word)
>
>> The one solution is to add merging tool/commnand which merges two
>> configuration file while maintaining the consistency.
>> This topic have been already discussed?
>
> Yes.  The entire reason that postgresql.auto.conf is separate is that
> we despaired of reading and rewriting postgresql.conf automatically
> without making a hash of material in the comments.  Calling the logic
> a "merge tool" does not make that problem go away.
>

The merge tool do not only to merge the all parameters in two
configuration into one file but also to remove duplicate parameters.
That is, the configuration files will be one file in logically.

It will be clearly complicated work that the user need to rewrite
postgresql.conf manually while maintaining the consistency.
(On top of that, The executing of ALTER SYSTEM command is not allowed
user except super user.)

Is it bad that ALTER SYSTEM parses postgresql.conf again at
AlterSystemSetConfigFile() to get line number and file name of that
parameter?
(or postgresql continue to keep line number and file name of parameter)


Regards,

-------
Sawada Masahiko

Sawada Masahiko <sawada.mshk@gmail.com> writes:
> On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yes.  The entire reason that postgresql.auto.conf is separate is that
>> we despaired of reading and rewriting postgresql.conf automatically
>> without making a hash of material in the comments.  Calling the logic
>> a "merge tool" does not make that problem go away.

> The merge tool do not only to merge the all parameters in two
> configuration into one file but also to remove duplicate parameters.
> That is, the configuration files will be one file in logically.

I'll just say one more time that if we thought this could work, we'd not
have arrived at the separate-files design to begin with.  You can work
on it if you like, but I will bet a good deal that you will not end up
with something that gets accepted.
        regards, tom lane

On Thu, Jan 15, 2015 at 1:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sawada Masahiko <sawada.mshk@gmail.com> writes:
>> On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Yes.  The entire reason that postgresql.auto.conf is separate is that
>>> we despaired of reading and rewriting postgresql.conf automatically
>>> without making a hash of material in the comments.  Calling the logic
>>> a "merge tool" does not make that problem go away.
>
>> The merge tool do not only to merge the all parameters in two
>> configuration into one file but also to remove duplicate parameters.
>> That is, the configuration files will be one file in logically.
>
> I'll just say one more time that if we thought this could work, we'd not
> have arrived at the separate-files design to begin with.  You can work
> on it if you like, but I will bet a good deal that you will not end up
> with something that gets accepted.
>

Yep, I don't intend to propose again that.
Because I thought that the maintaining of configuration file will be
complicated,
so I just thought we can add supporting tool.

Regards,

-------
Sawada Masahiko

----- Original Message -----


> From: Sawada Masahiko <sawada.mshk@gmail.com>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
> Sent: Wednesday, 14 January 2015, 16:09
> Subject: Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
> 
> On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>  Sawada Masahiko <sawada.mshk@gmail.com> writes:
>>>  The postgresql.auto.conf is loaded after loading of postgresql.conf
>>>  whenever configuration file is loaded or reloaded.
>>>  This means that parameter in postgresql.auto.conf is quite high
>>>  priority, so the parameter in postgresql.conf does not work at all
>>>  even if user set it manually.
>> 
>>>  If user want to change stopped postgresql server then user need to
>>>  merge two configuration file(postgresql.conf and postgresql.auto.conf)
>>>  while maintaining the consistency manually.
>> 
>>>>  From an operational perspective having a written config with 
> duplicate
>>>  entries is not good thing.
>>>  I think we need to merge two configuration file into one (or more than
>>>  one, if it uses like 'include' word)
>> 
>>>  The one solution is to add merging tool/commnand which merges two
>>>  configuration file while maintaining the consistency.
>>>  This topic have been already discussed?
>> 
>>  Yes.  The entire reason that postgresql.auto.conf is separate is that
>>  we despaired of reading and rewriting postgresql.conf automatically
>>  without making a hash of material in the comments.  Calling the logic
>>  a "merge tool" does not make that problem go away.
>> 
> 
> The merge tool do not only to merge the all parameters in two
> configuration into one file but also to remove duplicate parameters.
> That is, the configuration files will be one file in logically.

I think the point is that you can't really interpret the context of comments with an automated tool, so you'd probably
constructa misleading conf file.  I created something to mash conf files together in perl, but mash them together is
allit does: https://github.com/glynastill/pg_upgrade_conf
 

On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Wed, Jan 14, 2015 at 9:01 PM, Sawada Masahiko <sawada.mshk@gmail.com>
> wrote:
>>
>> Hi all,
>>
>> The postgresql.auto.conf is loaded after loading of postgresql.conf
>> whenever configuration file is loaded or reloaded.
>> This means that parameter in postgresql.auto.conf is quite high
>> priority, so the parameter in postgresql.conf does not work at all
>> even if user set it manually.
>>
>> If user want to change stopped postgresql server then user need to
>> merge two configuration file(postgresql.conf and postgresql.auto.conf)
>> while maintaining the consistency manually.
>>
>
> I think one way to currently do it is with the help of Alter System .. Reset
> command.  Basically user can check via pg_settings, if the variable
> belongs to postgresql.auto.conf and he knows already a duplicate copy
> of same is available in postrgresql.conf and that is the value he want to
> use, then RESET command could be used to remove the setting from
> postgresql.auto.conf
>

It sounds convenient.
e.g., It can reset one variable using by like RESET command, and reset
all variable using by RESET ALL command

>> From an operational perspective having a written config with duplicate
>> entries is not good thing.
>> I think we need to merge two configuration file into one (or more than
>> one, if it uses like 'include' word)
>>
>> The one solution is to add merging tool/commnand which merges two
>> configuration file while maintaining the consistency.
>
> If you want to think of some solution which can make the usage of Alter
> System more convenient, then we should think only in terms of change/
> remove the value in postgresql.auto.conf as that is the place where we
> have added values programatically.
>
> 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.


Regards,

-------
Sawada Masahiko

On Fri, Jan 16, 2015 at 12:54 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Thu, Jan 15, 2015 at 9:48 PM, Sawada Masahiko <sawada.mshk@gmail.com>
> wrote:
>> On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila <amit.kapila16@gmail.com>
>> 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.

>
> I don't know how appealing it would be to others, but a new view
> like pg_file_settings which would display the settings in file could
> be meaningful for your need.
>
> Another way is user can do pg_reload_conf() to see the latest
> values (excluding values for server startup time parameters).
>

I prefer the former. Because the latter would not handle all guc
variables as you said.
The new view like pg_file_setting has guc variable and config file
which has corresponding guc variable.
It would be helpful view for like ALTER SYSTEM ... RESET and that
command might be beneficial feature for user who want to manage
configuration file manually, I would like to propose them.

Regards,

-------
Sawada Masahiko

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.

On Sat, Jan 17, 2015 at 2:24 PM, David Johnston
<david.g.johnston@gmail.com> wrote:
> On Fri, Jan 16, 2015 at 10:19 PM, Amit Kapila <amit.kapila16@gmail.com>
> wrote:
>>
>> On Sat, Jan 17, 2015 at 10:41 AM, David Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > On Fri, Jan 16, 2015 at 10:08 PM, Amit Kapila <amit.kapila16@gmail.com>
>> > wrote:
>> >>
>> >> On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston
>> >> <david.g.johnston@gmail.com> wrote:
>> >> > > 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)
>> >>
>> >> Do we need to perform this parsing whenever user queries pg_settings?
>> >> I think it might lead to extra cycles of reading file when user won't
>> >> even
>> >> need it and as the code is shared with SHOW commands that could be
>> >> slightly complicated.
>> >>
>> >
>> > There would be no parsing upon reading of pg_settings, only lookups.
>> > The existing parsing would simply have its values saved to the catalogs that
>> > will be involved in the underlying pg_setting view query.
>> >
>> So are you telling that whenever we read, save the settings
>> to some catalog (probably a new one)?
>>
>> Will that completely address the problem specified in this thread,
>> as those values could probably be old (when last time server is
>> started or at last SIGHUP time values)?
>>
>
> Cache invalidation is a hard problem to solve :)
>
> I am reasonably content with showing the user who has just updated their
> postgresql.conf file and boots/SIGHUPs the server to find that said value
> hasn't taken effect that their value is indeed sitting in postgresql.conf
> but that other parts of the system are preempting it from being the active
> value.
>

I think it is enough to parse and store them only when server is
started and received SIGHUP.
(Probably we should add new function to read(not load) config files to
updated these new values in pg_settings like pg_read_conf())

In idea David suggested, we would not able to know name of included
file in postgresql.conf, and duplicated values in postgresql.conf.
Are they unnecessary?

Regards,

-------
Sawada Masahiko

On Sat, Jan 17, 2015 at 12:19 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> So are you telling that whenever we read, save the settings
> to some catalog (probably a new one)?

Which process are you imagining would do this?  Certainly not the postmaster.

Independently of that, it sounds like solving the problem from the
wrong end.  I think the idea of ALTER SYSTEM .. SET ought to be that
you should EITHER edit postgresql.conf for all your configuration
changes, OR you should use ALTER SYSTEM .. SET for all of your
changes.  If you mix-and-match, well, that's certainly allowed and it
will work and everything, but you - as a human being - might get
confused.  I am doubtful that any technological measures we take can
ever eliminate that confusion, because it doesn't result from some
defect of the system design, but rather from the fact that splitting
up your settings between multiple locations is inherently confusing,
especially if some settings are present in multiple locations with one
value overriding the others.

Imagine that you went out and bought a second wallet or purse.  Then
you take half of the stuff that is in your original one and put it in
the new one.  Then you order duplicates of 20% of the items and put
the copies in the wallet or purse that doesn't already contain a copy
of that item.  I predict that if you do this, you will sometimes get
confused about which one contains any particular item that you're
looking for.  But this is not anyone's fault except yours, and the
solution is simple: keep everything in one place.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 19, 2015 at 10:39 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> I think whichever process reads postgresql.conf/postgresql.auto.conf have
> to do this (unless we restrict that this will be done at some other time)
> and
> postmaster is one of them.  It seems to me that it is not good idea unless
> we do it at other time like populating entries for a view.

Well, the postmaster does not have database access, and neither do
most of the auxiliary processes.  The autovacuum launcher has very
limited database access (shared catalogs only).

Making the postmaster access the database is a complete non-starter;
we have worked very hard to avoid that, for reasons of overall system
robustness.  If the postmaster crashes or locks up, manual
intervention is required; if any other process crashes, the postmaster
can reset the whole system.

>> Independently of that, it sounds like solving the problem from the
>> wrong end.  I think the idea of ALTER SYSTEM .. SET ought to be that
>> you should EITHER edit postgresql.conf for all your configuration
>> changes, OR you should use ALTER SYSTEM .. SET for all of your
>> changes.  If you mix-and-match, well, that's certainly allowed and it
>> will work and everything, but you - as a human being - might get
>> confused.
>
> Right, but we can't completely eliminate such a possibility (as an
> example we have some default settings like max_connections,
> shared_buffers, etc).  I agree with you that users should use only
> way of changing the settings, however for certain rare cases (default
> settings or some other) we can provide a way for user to know which
> of the settings are duplicate.  I think if we can provide such an
> information via some view with ease then it's worth to have it.

I'd suggest abandoning the idea of storing anything in a persistent
basis in a system catalog and look for some way for the backend to
which the user is connected to expose its own state instead.  For
example, pg_settings already exposes "sourcefile" and "sourceline"
settings.  Actually, I'm not quite sure why that's not sufficient
here, but if it isn't, it could be extended.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

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

> 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,
thenwe 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'sworth 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

On Tue, Jan 20, 2015 at 4:07 PM, David Johnston
<david.g.johnston@gmail.com> wrote:
> 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.

This last proposal seems pointless to me.  If the user knows about
pg_db_role_setting, they will know to check it; if they don't, a
counter won't fix that.  I can see that there might be some utility to
a query that would tell you, for a given setting, all sources of that
setting the system knew about, whether in configuration files,
pg_db_role_setting, or the current session.  But I don't see that
putting information that's already available via one system catalog
query into a different system catalog query helps anything - we should
presume DBAs know how to write SQL.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jan 21, 2015 at 3:38 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> Okay and I was also not in favour of this approach.
>

Okay I agree with this.

>
> The reason why "sourcefile" and "sourceline" are not sufficient is that
> they can only give the information about the setting in last file it is
> present.  Assume max_connections (or any other setting) is available
> in both postgresql.conf and postgresql.auto.conf, then it will display
> the information about the setting in postgresql.auto.conf, so now user
> might not be able to decide whether that is the setting he want to retain
> unless he knows the information about setting in postgresql.conf.
>
> Now as I have suggested upthread, that we can have a new view
> pg_file_settings which will display information about settings even
> when there exists multiple entries for the same in different files.
>
> I think adding such information to existing view pg_settings would
> be difficult as the same code is used for show commands which
> we don't want to change.
>

I think this new view is updated only when postmaster received SIGHUP
or is started.
And we can have new function like pg_update_file_setting() which
updates this view.

Regards,

-------
Sawada Masahiko

On Thu, Jan 22, 2015 at 11:41 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Wed, Jan 21, 2015 at 9:43 PM, Sawada Masahiko <sawada.mshk@gmail.com>
> wrote:
>>
>> On Wed, Jan 21, 2015 at 3:38 PM, Amit Kapila <amit.kapila16@gmail.com>
>> wrote:
>> > On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas <robertmhaas@gmail.com>
>> > wrote:
>> >
>> >
>> > The reason why "sourcefile" and "sourceline" are not sufficient is that
>> > they can only give the information about the setting in last file it is
>> > present.  Assume max_connections (or any other setting) is available
>> > in both postgresql.conf and postgresql.auto.conf, then it will display
>> > the information about the setting in postgresql.auto.conf, so now user
>> > might not be able to decide whether that is the setting he want to
>> > retain
>> > unless he knows the information about setting in postgresql.conf.
>> >
>> > Now as I have suggested upthread, that we can have a new view
>> > pg_file_settings which will display information about settings even
>> > when there exists multiple entries for the same in different files.
>> >
>> > I think adding such information to existing view pg_settings would
>> > be difficult as the same code is used for show commands which
>> > we don't want to change.
>> >
>>
>> I think this new view is updated only when postmaster received SIGHUP
>> or is started.
>> And we can have new function like pg_update_file_setting() which
>> updates this view.
>>
>
> If that is doable without much complication, then it might not be bad
> idea to just add additional columns to existing view (pg_settings).  I
> think you can once evaluate the details like what additional columns
> (other than what pg_settings has) are required and how you want to
> update them. After doing so further discussion could be more meaningful.
>
>

I have posted mail as another thread to discuss about this.
<CAD21AoA8_MEjMNDHCEKT8CDyFUp8FpBoxAP0N0-LXErvV7fZXg@mail.gmail.com>

Regards,

-------
Sawada Masahiko

On Wed, Jan 21, 2015 at 1:32 AM, David Johnston
<david.g.johnston@gmail.com> wrote:
> to make the whole thing work.  Maybe it does all fit directly on pg_settings
> but tacking on some read-only columns to this updateable view/table doesn't
> come across as something that should be forbidden in general.

No, of course not.  But they should be things that are of general
utility (we agree that most people will want them) and they should be
orthogonal to what we already have (not just a duplication of
something that's present elsewhere).

> Maybe I am imagining a use-case that just isn't there but if there are two
> separate queries needed, and we call one "consolidated", then having that
> query indicate whether the other query has useful information, and it is
> quite likely that it will not, avoids the user expending the effort to run
> the wasteful secondary query.

Well, we shouldn't assume that everyone uses the same queries, or
issues them in the same order, so I think this is pretty speculative.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jan 21, 2015 at 1:38 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> Now as I have suggested upthread, that we can have a new view
> pg_file_settings which will display information about settings even
> when there exists multiple entries for the same in different files.
>
> I think adding such information to existing view pg_settings would
> be difficult as the same code is used for show commands which
> we don't want to change.

A new view might work, or we could add columns that contain arrays to
the existing view.  But a new view may be nicer.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jan 21, 2015 at 11:13 AM, Sawada Masahiko <sawada.mshk@gmail.com> wrote:
> I think this new view is updated only when postmaster received SIGHUP
> or is started.
> And we can have new function like pg_update_file_setting() which
> updates this view.

I really don't think the postmaster should be in the business of
trying to publish views of the data.  Let the individual backend
reveal its own view, and keep the postmaster out of it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company