Proposal: knowing detail of config files via SQL

On 1/22/15 11:13 AM, Sawada Masahiko wrote:

Hi,

As per discussion
</messages/by-id/CAD21AoDkds8Oqbr199wwrCp7fiDvOw6bbb+CGdwQHUF+gX_bVg@mail.gmail.com&gt;,
I would like to proposal new view like pg_file_settings to know detail
of config file via SQL.

- Background
In 9.4 postgresql.auto.conf is added to support ALTER SYSTEM command
and that config file is loaded after whenever postgresql.conf is
loaded.
That is, postgresql.auto.conf is quite high priority so that the value
in postgresql.conf can not work at all if DBA set it manually.
ALTER SYSTEM RESET command can remove the unnecessary value in
postgresql.auto.conf but there are no way to know about where the
value has came from.
(They can only give the information about the setting in last file it
is present.)

- Solution
The patch not is implemented yet, just proposing now.
I'm imaging that we can have new pg_file_settings view has following
column to store current assigned value in config file.
- guc value name
- guc value
- config file path (e.g. /opt/data/postgresql.sql,
/opt/data/postgresql.auto.conf, /opt/hoge.conf)
This view could be convenient for DBA to decide if the
postgresql.auto.conf is useful or not and if it's not useful then DBA
could use ALTER SYSTEM .. RESET command to remove the same from
postgresql.auto.conf.

Would this view have a row for every option in a config file? IE: if you set something in both postgresql.conf and postgresql.auto.conf, would it show up twice? I think it should, and that there should be a way to see which setting is actually in effect.

It looks like you're attempting to handle #include, yes?

Also other idea is to add additional columns existing view
(pg_settings), according to prev discussion.

I think it would be useful to have a separate view that shows all occurrences of a setting. I recall some comment about source_file and source_line not always being correct in pg_settings; if that's true we should fix that.
--
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

Sawada Masahiko <sawada.mshk@gmail.com> writes:

As per discussion
</messages/by-id/CAD21AoDkds8Oqbr199wwrCp7fiDvOw6bbb+CGdwQHUF+gX_bVg@mail.gmail.com&gt;,
I would like to proposal new view like pg_file_settings to know detail
of config file via SQL.

- Background
In 9.4 postgresql.auto.conf is added to support ALTER SYSTEM command
and that config file is loaded after whenever postgresql.conf is
loaded.
That is, postgresql.auto.conf is quite high priority so that the value
in postgresql.conf can not work at all if DBA set it manually.
ALTER SYSTEM RESET command can remove the unnecessary value in
postgresql.auto.conf but there are no way to know about where the
value has came from.
(They can only give the information about the setting in last file it
is present.)

- Solution
The patch not is implemented yet, just proposing now.
I'm imaging that we can have new pg_file_settings view has following
column to store current assigned value in config file.
- guc value name
- guc value
- config file path (e.g. /opt/data/postgresql.sql,
/opt/data/postgresql.auto.conf, /opt/hoge.conf)
This view could be convenient for DBA to decide if the
postgresql.auto.conf is useful or not and if it's not useful then DBA
could use ALTER SYSTEM .. RESET command to remove the same from
postgresql.auto.conf.

Also other idea is to add additional columns existing view
(pg_settings), according to prev discussion.

Please give me comments.

I still don't understand what problem you think needs to be solved.
It's already perfectly clear from the pg_settings view when a value
came from postgresql.auto.conf. For instance:

regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';
name | setting | source | sourcefile | sourceline
----------+------------+--------------------+-------------------------------------------------+------------
TimeZone | US/Eastern | configuration file | /home/postgres/testversion/data/postgresql.conf | 531
(1 row)

regression=# alter system set timezone = 'Asia/Shanghai';
ALTER SYSTEM
regression=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';
name | setting | source | sourcefile | sourceline
----------+---------------+--------------------+------------------------------------------------------+------------
TimeZone | Asia/Shanghai | configuration file | /home/postgres/testversion/data/postgresql.auto.conf | 3
(1 row)

regression=# alter system reset timezone;
ALTER SYSTEM
regression=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

regression=# select name,setting,source,sourcefile,sourceline from pg_settings where name = 'TimeZone';
name | setting | source | sourcefile | sourceline
----------+------------+--------------------+-------------------------------------------------+------------
TimeZone | US/Eastern | configuration file | /home/postgres/testversion/data/postgresql.conf | 531
(1 row)

What else is needed?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane-2 wrote

regression=# alter system reset timezone;
ALTER SYSTEM
regression=# select pg_reload_conf();

How does someone know that performing the above commands will result in the
TimeZone setting being changed from Asia/Shanghai to US/Eastern?

David J.

--
View this message in context: http://postgresql.nabble.com/Proposal-knowing-detail-of-config-files-via-SQL-tp5835075p5835142.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David G Johnston <david.g.johnston@gmail.com> writes:

Tom Lane-2 wrote

regression=# alter system reset timezone;
ALTER SYSTEM
regression=# select pg_reload_conf();

How does someone know that performing the above commands will result in the
TimeZone setting being changed from Asia/Shanghai to US/Eastern?

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David G Johnston <david.g.johnston@gmail.com> writes:

Tom Lane-2 wrote

regression=# alter system reset timezone;
ALTER SYSTEM
regression=# select pg_reload_conf();

How does someone know that performing the above commands will result in

the

TimeZone setting being changed from Asia/Shanghai to US/Eastern?

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

​The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

David J.
​

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

​The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?
And if they do need to know whether a variable is set in postgresql.conf,
how often wouldn't they just resort to "grep" instead? (Among other
points, grep would succeed at noticing commented-out entries, which this
mechanism would not.)

GUC has existed in more or less its current state for about 15 years,
and I don't recall a lot of complaints that would be solved by this.
Furthermore, given that ALTER SYSTEM was sold to us as more or less
obsoleting manual editing of postgresql.conf, I rather doubt that it's
changed the basis of discussion all that much.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 22, 2015 at 3:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

​The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order,

so

that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1

and

2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that

what

the value is in postgresql.conf that would become active. Furthermore,

if

postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?
And if they do need to know whether a variable is set in postgresql.conf,
how often wouldn't they just resort to "grep" instead? (Among other
points, grep would succeed at noticing commented-out entries, which this
mechanism would not.)

GUC has existed in more or less its current state for about 15 years,
and I don't recall a lot of complaints that would be solved by this.
Furthermore, given that ALTER SYSTEM was sold to us as more or less
obsoleting manual editing of postgresql.conf, I rather doubt that it's
changed the basis of discussion all that much.

​i doubt we'd actually see many complaints since, like you say, people are
likely to just use a different technique. The only thing PostgreSQL itself
needs to provide is a master inventory of seen/known settings; the user
interface can be left up to other layers (psql, pgadmin, extensions,
etc...). It falls into making the system more user friendly. But maybe
the answer for those users is that if you setup is so complex this would be
helpful you probably need to rethink your setup. Then again, if I only
interact with the via SQL at least can issue RESET ​and know the end result
- after a config reload - without having to log into the server and grep
the config file - or know what the system defaults are for settings that do
not appear explicitly in postgresql.conf; all without having to refer to
documentation as well.

I'm doubtful it is going to interest any of the core hackers to put this in
place but it at least warrants a couple of passes of brain-storming which
can then be memorialized on the Wiki-ToDo.

David J.

On 01/22/2015 02:09 PM, David Johnston wrote:

​The proposal provides for SQL access to all possible sources of
variable value setting and, ideally, a means of ordering them in
priority order, so that a search for TimeZone would return two records,
one for postgresql.auto.conf and one for postgresql.conf - which are
numbered 1 and 2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that
what the value is in postgresql.conf that would become active.
Furthermore, if postgresql.conf has a setting AND there is a mapping in
an #included file that information would be accessible via SQL as well.

Wow. Um, I can't imagine any use for that which would justify the
overhead. And I'm practically the "settings geek".

Note that a single file can have multiple copies of the same GUC, plus
there's GUCs set interactively, as well as in the user and database
properties. So you're looking at a lot of different "versions".

I think you're in a position of needing to interest someone else in this
issue enough to produce a patch to argue about. I'm not seeing a lot of
interest in it here.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 23, 2015 at 3:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I would say not that often as I think nobody changes the settings in
configuration files every now and then, but it could still be meaningful
in situations as described upthread by Sawada.

I think similar to this, pg_reload_conf() or many such things will be used
not that frequently, it seems to me that here important point to consider
is that whether such a view could serve any purpose for real users?

If we see multiple value for same config parameter was possible even
previous to Alter System and there doesn't seem to be much need/demand
for such a view and the reason could be that user has no way of changing
the setting at file level with command, but now as we provide a way it
could be useful in certain cases when user want to retain previous
values (value in postgresql.conf).

I understand that usage of such a view is not that high, but it could be
meaningful in some cases.

And if they do need to know whether a variable is set in postgresql.conf,
how often wouldn't they just resort to "grep" instead?

Do always user/dba (having superuser privilege) access to postgresql.conf
file? It seems to me even if they have access, getting the information via
SQL would be more appealing.

(Among other
points, grep would succeed at noticing commented-out entries, which this
mechanism would not.)

GUC has existed in more or less its current state for about 15 years,
and I don't recall a lot of complaints that would be solved by this.
Furthermore, given that ALTER SYSTEM was sold to us as more or less
obsoleting manual editing of postgresql.conf, I rather doubt that it's
changed the basis of discussion all that much.

By providing such a view I don't mean to recommend the user to change
the settings by editing postgresql.conf manually and then use Alter System
for other cases, rather it could be used for some cases like for default
values
or if in rare cases user has changed the parameters manually.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Fri, Jan 23, 2015 at 4:36 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

Would this view have a row for every option in a config file? IE: if you set
something in both postgresql.conf and postgresql.auto.conf, would it show up
twice? I think it should, and that there should be a way to see which
setting is actually in effect.

yes.

It looks like you're attempting to handle #include, yes?

Of course yes.

Also other idea is to add additional columns existing view
(pg_settings), according to prev discussion.

I think it would be useful to have a separate view that shows all
occurrences of a setting. I recall some comment about source_file and
source_line not always being correct in pg_settings; if that's true we
should fix that.

You mean that pg_settings view shows the variable in current session?
pg_settings view shows can be changed if user set the GUC parameter in
session or GUC parameter is set to role individually.
But I don't think pg_file_settings view has such a behavior.

Regards,

-------
Sawada Masahiko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I believe I've run into situations where this would be useful. I
wouldn't be willing to put in the effort to do this myself, but I
wouldn't be prepared to vote against a high-quality patch that someone
else felt motivated to write.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jan 27, 2015 at 3:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I believe I've run into situations where this would be useful. I
wouldn't be willing to put in the effort to do this myself, but I
wouldn't be prepared to vote against a high-quality patch that someone
else felt motivated to write.

Attached patch adds new view pg_file_settings (WIP version).
This view behaves like followings,
[postgres][5432](1)=# select * from pg_file_settings ;
name | setting |
sourcefile
----------------------------+--------------------+--------------------------------------------------------
max_connections | 100 |
/home/masahiko/pgsql/master/3data/postgresql.conf
shared_buffers | 128MB |
/home/masahiko/pgsql/master/3data/postgresql.conf
dynamic_shared_memory_type | posix |
/home/masahiko/pgsql/master/3data/postgresql.conf
log_timezone | Japan |
/home/masahiko/pgsql/master/3data/postgresql.conf
datestyle | iso, mdy |
/home/masahiko/pgsql/master/3data/postgresql.conf
timezone | Japan |
/home/masahiko/pgsql/master/3data/postgresql.conf
lc_messages | C |
/home/masahiko/pgsql/master/3data/postgresql.conf
lc_monetary | C |
/home/masahiko/pgsql/master/3data/postgresql.conf
lc_numeric | C |
/home/masahiko/pgsql/master/3data/postgresql.conf
lc_time | C |
/home/masahiko/pgsql/master/3data/postgresql.conf
default_text_search_config | pg_catalog.english |
/home/masahiko/pgsql/master/3data/postgresql.conf
work_mem | 128MB |
/home/masahiko/pgsql/master/3data/hoge.conf
checkpoint_segments | 300 |
/home/masahiko/pgsql/master/3data/hoge.conf
enable_indexscan | off |
/home/masahiko/pgsql/master/3data/postgresql.auto.conf
work_mem | 64MB |
/home/masahiko/pgsql/master/3data/postgresql.auto.conf

[postgres][5432](1)=# select name, setting from pg_settings where name
= 'work_mem';
name | setting
----------+---------
work_mem | 65536
(1 row)

Above query result shows that both hoge.conf and postgresql.auto.conf
have same config parameter work_mem, and work_mem in auto.conf is
effecitve.
We can confirm these parameter to decide if the postgresql.auto.conf
is useful or not.

Regards,

-------
Sawada Masahiko

On Fri, Jan 30, 2015 at 09:38:10PM +0900, Sawada Masahiko wrote:

On Tue, Jan 27, 2015 at 3:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I believe I've run into situations where this would be useful. I
wouldn't be willing to put in the effort to do this myself, but I
wouldn't be prepared to vote against a high-quality patch that someone
else felt motivated to write.

Attached patch adds new view pg_file_settings (WIP version).
This view behaves like followings,
[postgres][5432](1)=# select * from pg_file_settings ;
name | setting |
sourcefile
----------------------------+--------------------+--------------------------------------------------------
max_connections | 100 |
/home/masahiko/pgsql/master/3data/postgresql.conf
shared_buffers | 128MB |
/home/masahiko/pgsql/master/3data/postgresql.conf

This looks great!

Is there a reason not to have the sourcefile as a column in
pg_settings?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jan 31, 2015 at 12:24 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 30, 2015 at 09:38:10PM +0900, Sawada Masahiko wrote:

On Tue, Jan 27, 2015 at 3:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I believe I've run into situations where this would be useful. I
wouldn't be willing to put in the effort to do this myself, but I
wouldn't be prepared to vote against a high-quality patch that someone
else felt motivated to write.

Attached patch adds new view pg_file_settings (WIP version).
This view behaves like followings,
[postgres][5432](1)=# select * from pg_file_settings ;
name | setting |
sourcefile
----------------------------+--------------------+--------------------------------------------------------
max_connections | 100 |
/home/masahiko/pgsql/master/3data/postgresql.conf
shared_buffers | 128MB |
/home/masahiko/pgsql/master/3data/postgresql.conf

This looks great!

Is there a reason not to have the sourcefile as a column in
pg_settings?

pg_file_settings view also has source file column same as pg_settings.
It might was hard to confirm that column in previous mail.
I put example of pg_file_settings again as follows.

[postgres][5432](1)=# select * from pg_file_settings where name = 'work_mem';
-[ RECORD 1 ]------------------------------------------------------
name | work_mem
setting | 128MB
sourcefile | /home/masahiko/pgsql/master/3data/hoge.conf
-[ RECORD 2 ]------------------------------------------------------
name | work_mem
setting | 64MB
sourcefile | /home/masahiko/pgsql/master/3data/postgresql.auto.conf

Regards,

-------
Sawada Masahiko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

​This would default to being available to superusers only, right? Details
of the file system shouldn't be available to any random user.​

__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com

<http://www.rrdonnelley.com/&gt;
* <Mike.Blackwell@rrd.com>*

On Fri, Jan 30, 2015 at 9:50 AM, Sawada Masahiko <sawada.mshk@gmail.com>
wrote:

On Sat, Jan 31, 2015 at 12:58 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote:

This would default to being available to superusers only, right? Details of
the file system shouldn't be available to any random user.

This WIP patch does not behave like that, but I agree.
This view would be effective combine with ALTER SYSTEM, and ALTER
SYSTEM command is executable to superuser only also.

Regards,

-------
Sawada Masahiko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jan 31, 2015 at 12:50:20AM +0900, Sawada Masahiko wrote:

On Sat, Jan 31, 2015 at 12:24 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 30, 2015 at 09:38:10PM +0900, Sawada Masahiko wrote:

On Tue, Jan 27, 2015 at 3:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 22, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Jan 22, 2015 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is that a requirement, and if so why? Because this proposal doesn't
guarantee any such knowledge AFAICS.

The proposal provides for SQL access to all possible sources of variable
value setting and, ideally, a means of ordering them in priority order, so
that a search for TimeZone would return two records, one for
postgresql.auto.conf and one for postgresql.conf - which are numbered 1 and
2 respectively - so that in looking at that result if the
postgresql.auto.conf entry were to be removed the user would know that what
the value is in postgresql.conf that would become active. Furthermore, if
postgresql.conf has a setting AND there is a mapping in an #included file
that information would be accessible via SQL as well.

I know what the proposal is. What I am questioning is the use-case that
justifies having us build and support all this extra mechanism. How often
does anyone need to know what the "next down" variable value would be?

I believe I've run into situations where this would be useful. I
wouldn't be willing to put in the effort to do this myself, but I
wouldn't be prepared to vote against a high-quality patch that someone
else felt motivated to write.

Attached patch adds new view pg_file_settings (WIP version).
This view behaves like followings,
[postgres][5432](1)=# select * from pg_file_settings ;
name | setting |
sourcefile
----------------------------+--------------------+--------------------------------------------------------
max_connections | 100 |
/home/masahiko/pgsql/master/3data/postgresql.conf
shared_buffers | 128MB |
/home/masahiko/pgsql/master/3data/postgresql.conf

This looks great!

Is there a reason not to have the sourcefile as a column in
pg_settings?

pg_file_settings view also has source file column same as pg_settings.
It might was hard to confirm that column in previous mail.
I put example of pg_file_settings again as follows.

[postgres][5432](1)=# select * from pg_file_settings where name = 'work_mem';
-[ RECORD 1 ]------------------------------------------------------
name | work_mem
setting | 128MB
sourcefile | /home/masahiko/pgsql/master/3data/hoge.conf
-[ RECORD 2 ]------------------------------------------------------
name | work_mem
setting | 64MB
sourcefile | /home/masahiko/pgsql/master/3data/postgresql.auto.conf

Masuhiko-san,

I apologize for not communicating clearly. My alternative proposal is
to add a NULL-able sourcefile column to pg_settings. This is as an
alternative to creating a new pg_file_settings view.

Why might the contents of pg_settings be different from what would be
in pg_file_settings, apart from the existence of this column?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 30, 2015 at 12:05 PM, David Fetter <david@fetter.org> wrote:

On Sat, Jan 31, 2015 at 12:50:20AM +0900, Sawada Masahiko wrote:

[postgres][5432](1)=# select * from pg_file_settings where name =

'work_mem';

-[ RECORD 1 ]------------------------------------------------------
name | work_mem
setting | 128MB
sourcefile | /home/masahiko/pgsql/master/3data/hoge.conf
-[ RECORD 2 ]------------------------------------------------------
name | work_mem
setting | 64MB
sourcefile | /home/masahiko/pgsql/master/3data/postgresql.auto.conf

Masuhiko-san,

I apologize for not communicating clearly. My alternative proposal is
to add a NULL-able sourcefile column to pg_settings. This is as an
alternative to creating a new pg_file_settings view.

Why might the contents of pg_settings be different from what would be
in pg_file_settings, apart from the existence of this column?

​
​​The contents of pg_settings uses the setting name as a primary key.

The contents of pg_file_setting uses a compound key consisting of the
setting name and the source file.

See "work_mem" in the provided example.

More specifically pg_settings only care about the "currently active
setting" while this cares about "inactive" settings as well.

David J.
​

David Johnston <david.g.johnston@gmail.com> writes:

On Fri, Jan 30, 2015 at 12:05 PM, David Fetter <david@fetter.org> wrote:

Why might the contents of pg_settings be different from what would be
in pg_file_settings, apart from the existence of this column?

​​The contents of pg_settings uses the setting name as a primary key.
The contents of pg_file_setting uses a compound key consisting of the
setting name and the source file.

[ raised eyebrow... ] Seems insufficient in the case that multiple
settings of the same value occur in the same source file. Don't you
need a line number in the key as well?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers