Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Thu, Jan 22, 2015 at 11:41 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Jan 21, 2015 at 9:43 PM, Sawada Masahiko sawada.m...@gmail.com wrote: On Wed, Jan 21, 2015 at 3:38 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas robertmh...@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-lxervv7f...@mail.gmail.com 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
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 21, 2015 at 1:32 AM, David Johnston david.g.johns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 21, 2015 at 1:38 AM, Amit Kapila amit.kapil...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 21, 2015 at 11:13 AM, Sawada Masahiko sawada.m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 21, 2015 at 3:38 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 21, 2015 at 9:43 PM, Sawada Masahiko sawada.m...@gmail.com wrote: On Wed, Jan 21, 2015 at 3:38 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas robertmh...@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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Mon, Jan 19, 2015 at 10:39 PM, Amit Kapila amit.kapil...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
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
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Tue, Jan 20, 2015 at 1:24 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 1/16/15 10:32 PM, David G Johnston wrote: 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. OK 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. #include files still appear to come from postgresql.conf; I'm not proposing we try and memorize every single instance of a variable declaration and provide a global overlaps query - though that piece already seems to be in place... 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). Actually, there are not separate PER ROLE and PER DATABASE settings even though there are different SQL commands. The catalog is simply pg_db_role_setting with the use of all tags (i.e., 0) as necessary. But I see where you are going and do not disagree that precedence information could be useful. 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. 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; This is fine - but I'm thinking about situations where a user immediately SET ROLE on their session and typically operate as said user; if they try doing an ALTER ROLE SET for this SET ROLE user it will not work because their login user is what matters. This is probably a solution looking for a problem but it is a dynamic that one needs to be aware of. 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 My main concern with the UI would be too-much-information; but otherwise if we accept the premise that we should include as much as possible and then let the user (or us) provide more useful subsets based upon common use-cases the main issue is making sure we've identified all of the relevant information that needs to be captured - even if it is something as minor as the whole logon vs. current role dynamic. I'm ignoring the cost/benefit aspect of implementation for the moment largely because I do known enough about the backend to make reasonable comments. But much of the data is already present in various views and catalogs - I just think having one-stop-shop would be useful and go a long
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Tue, Jan 20, 2015 at 4:07 PM, David Johnston david.g.johns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Tue, Jan 20, 2015 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 20, 2015 at 4:07 PM, David Johnston david.g.johns...@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. While this is not exactly a high-traffic catalog/view why have them write a likely 4-way join query (pg_db_role_setting is not the friendly in its current form), or even two separate queries, when we can give them a solid and comprehensive view standard. I guess part of the mixup is that I am basically talking about a view of multiple catalogs as a DBA UI as opposed to really even caring what specific catalogs (existing or otherwise) or functions are needed 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. 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. David J.
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Tue, Jan 20, 2015 at 9:38 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 19, 2015 at 10:39 PM, Amit Kapila amit.kapil...@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; Okay and I was also not in favour of this approach. 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. Agreed. 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. 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Tue, Jan 20, 2015 at 9:09 AM, Amit Kapila amit.kapil...@gmail.com wrote: 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 Sorry for incomplete sentence, I mean to say only one 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Mon, Jan 19, 2015 at 9:35 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jan 17, 2015 at 12:19 AM, Amit Kapila amit.kapil...@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. 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. 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Sat, Jan 17, 2015 at 12:19 AM, Amit Kapila amit.kapil...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Sat, Jan 17, 2015 at 2:24 PM, David Johnston david.g.johns...@gmail.com wrote: On Fri, Jan 16, 2015 at 10:19 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:41 AM, David Johnston david.g.johns...@gmail.com wrote: On Fri, Jan 16, 2015 at 10:08 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston david.g.johns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Fri, Jan 16, 2015 at 12:54 PM, Amit Kapila amit.kapil...@gmail.com wrote:
On Thu, Jan 15, 2015 at 9:48 PM, Sawada Masahiko sawada.m...@gmail.com
wrote:
On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila amit.kapil...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Fri, Jan 16, 2015 at 9:55 PM, Sawada Masahiko sawada.m...@gmail.com wrote: On Fri, Jan 16, 2015 at 12:54 PM, Amit Kapila amit.kapil...@gmail.com wrote: 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. Okay, but I think it would be better to start a new thread as this proposal seems to be different than your original idea. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
Sawada Masahiko wrote
On Fri, Jan 16, 2015 at 12:54 PM, Amit Kapila lt;
amit.kapila16@
gt; wrote:
On Thu, Jan 15, 2015 at 9:48 PM, Sawada Masahiko lt;
sawada.mshk@
gt;
wrote:
On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila lt;
amit.kapila16@
gt;
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston david.g.johns...@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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Fri, Jan 16, 2015 at 10:08 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston david.g.johns...@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. David J.
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Sat, Jan 17, 2015 at 10:41 AM, David Johnston david.g.johns...@gmail.com wrote: On Fri, Jan 16, 2015 at 10:08 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston david.g.johns...@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)? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Fri, Jan 16, 2015 at 10:19 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:41 AM, David Johnston david.g.johns...@gmail.com wrote: On Fri, Jan 16, 2015 at 10:08 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Jan 17, 2015 at 10:02 AM, David G Johnston david.g.johns...@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. David J.
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Thu, Jan 15, 2015 at 9:48 PM, Sawada Masahiko sawada.m...@gmail.com
wrote:
On Thu, Jan 15, 2015 at 2:02 PM, Amit Kapila amit.kapil...@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.
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).
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Wed, Jan 14, 2015 at 9:01 PM, Sawada Masahiko sawada.m...@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 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
Sawada Masahiko sawada.m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Thu, Jan 15, 2015 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sawada Masahiko sawada.m...@gmail.com writes: On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sawada Masahiko sawada.m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
Sawada Masahiko sawada.m...@gmail.com writes: On Thu, Jan 15, 2015 at 12:37 AM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merging postgresql.conf and postgresql.auto.conf
- Original Message - From: Sawada Masahiko sawada.m...@gmail.com To: Tom Lane t...@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 t...@sss.pgh.pa.us wrote: Sawada Masahiko sawada.m...@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 construct a misleading conf file. I created something to mash conf files together in perl, but mash them together is all it does: https://github.com/glynastill/pg_upgrade_conf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
