Thread: Alter system and reload causes bogus complaints about setting changes
Hi all, playing with 9.4 beta: Consider: $ cat config.sql ALTER SYSTEM SET max_connections = 200; ALTER SYSTEM SET shared_buffers = "10GB"; ALTER SYSTEM SET maintenance_work_mem = "1GB"; ALTER SYSTEM SET effective_io_concurrency = 10; ALTER SYSTEM SET wal_buffers = "32MB"; ALTER SYSTEM SET checkpoint_segments = 192; ALTER SYSTEM SET checkpoint_completion_target = 0.8; $ psql < config.sql $ service postgresql restart $ psql ALTER SYSTEM SET checkpoint_segments = 384; \q $ service postgresql reload $ tail /var/log/postgresql/postgresql-9.4-main.log 2014-07-03 08:09:40 AST LOG: received SIGHUP, reloading configuration files 2014-07-03 08:09:40 AST LOG: parameter "max_connections" cannot be changed without restarting the server 2014-07-03 08:09:40 AST LOG: parameter "shared_buffers" cannot be changed without restarting the server 2014-07-03 08:09:40 AST LOG: parameter "checkpoint_segments" changed to "1920" 2014-07-03 08:09:40 AST LOG: configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected changes were applied Hmmm - I didn't actually change the settings it is complaining about...and it is referring to the wrong file too. While this is hardly serious, it is likely to be immensely confusing for folk who have not been actively following the new changes for 9.4 (ahem...that could be a lot of people). It would be good to fix this before we end beta. Cheers Mark
On 03/07/14 21:16, Mark Kirkwood wrote: > 2014-07-03 08:09:40 AST LOG: parameter "checkpoint_segments" changed to > "1920" Sorry - should be 384...looking at log from wrong server :-(
On Thu, Jul 3, 2014 at 2:46 PM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote: > > Hi all, playing with 9.4 beta: > > Consider: > > $ cat config.sql > ALTER SYSTEM SET max_connections = 200; > ALTER SYSTEM SET shared_buffers = "10GB"; > > $ psql < config.sql > > $ service postgresql restart > > $ psql > ALTER SYSTEM SET checkpoint_segments = 384; > \q > > $ service postgresql reload > > $ tail /var/log/postgresql/postgresql-9.4-main.log > 2014-07-03 08:09:40 AST LOG: received SIGHUP, reloading configuration files > 2014-07-03 08:09:40 AST LOG: parameter "max_connections" cannot be changed without restarting the server > 2014-07-03 08:09:40 AST LOG: parameter "shared_buffers" cannot be changed without restarting the server > 2014-07-03 08:09:40 AST LOG: parameter "checkpoint_segments" changed to "1920" > 2014-07-03 08:09:40 AST LOG: configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected changes were applied > > > Hmmm - I didn't actually change the settings it is complaining about...and it is referring to the wrong file too. While this is hardly serious, it is likely to be immensely confusing for folk who have not been actively following the new changes for 9.4 (ahem...that could be a lot of people). It would be good to fix this before we end beta. This issue occurs mainly due to duplicate settings in postgresql.conf and postgresql.auto.conf (file where ALTER SYSTEM writes). I have explained it in more detail in the below thread: http://www.postgresql.org/message-id/CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=PHEvBqLQkhdUV9cWd1A@mail.gmail.com With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On 04/07/14 04:57, Amit Kapila wrote: > On Thu, Jul 3, 2014 at 2:46 PM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> >> Hmmm - I didn't actually change the settings it is complaining > about...and it is referring to the wrong file too. While this is hardly > serious, it is likely to be immensely confusing for folk who have not been > actively following the new changes for 9.4 (ahem...that could be a lot of > people). It would be good to fix this before we end beta. > > This issue occurs mainly due to duplicate settings in postgresql.conf > and postgresql.auto.conf (file where ALTER SYSTEM writes). I have > explained it in more detail in the below thread: > > http://www.postgresql.org/message-id/CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=PHEvBqLQkhdUV9cWd1A@mail.gmail.com > > While you are correct about the cause (conflicting entries in postgresql.conf and the auto conf) - the statement 'just eliminate them' is not really acceptable. Why? because a completely default install via 'initdb' has settings uncommented in postgresql.conf, so anyone changing the most likely-to-be changed settings (ahem - shared_buffers for instance) will elicit this error when the change *something else* that only requires a reload. I.e this is not a problem created by the user. Also as I mentioned before, the error complains about the wrong file, so the other statement 'the user can take appropriate action' is rather disingenuous - how can they given the error is itself wrong? I predict this is going to be a huge source of confusion for new users, and at the very least the error message needs to be a) correct and b) actually tell the user the remedy. Regards Mark
On Fri, Jul 4, 2014 at 3:59 AM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote: > On 04/07/14 04:57, Amit Kapila wrote: > >>> Hmmm - I didn't actually change the settings it is complaining >> >> about...and it is referring to the wrong file too. While this is hardly >> serious, it is likely to be immensely confusing for folk who have not been >> actively following the new changes for 9.4 (ahem...that could be a lot of >> people). It would be good to fix this before we end beta. >> >> This issue occurs mainly due to duplicate settings in postgresql.conf >> and postgresql.auto.conf (file where ALTER SYSTEM writes). I have >> explained it in more detail in the below thread: >> >> http://www.postgresql.org/message-id/CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=PHEvBqLQkhdUV9cWd1A@mail.gmail.com >> >> > > While you are correct about the cause (conflicting entries in postgresql.conf and the auto conf) - the statement 'just eliminate them' is not really acceptable. Why? because a completely default install via 'initdb' has settings uncommented in postgresql.conf, so anyone changing the most likely-to-be changed settings (ahem - shared_buffers for instance) will elicit this error when the change *something else* that only requires a reload. I.e this is not a problem created by the user. True, but as I explained in that thread as well that this is an existing behaviour which is also visible via Alter System, so changing an existing mechanism requires non-trivial fix (eliminating duplicate entries during processing of config file) which I think can be risky doing at this time in terms of impact, however we can try to minimise the inconvenience user can face due to this feature. > Also as I mentioned before, the error complains about the wrong file, so the other statement 'the user can take appropriate action' is rather disingenuous - how can they given the error is itself wrong? Are you referring to ("postgresql.conf") in below messages? If yes, then the file mentioned in error message is right, as the uncommented conflicting value in postgresql.conf is the reason for this message. > 2014-07-03 08:09:40 AST LOG: parameter "max_connections" cannot be changed without restarting the server > 2014-07-03 08:09:40 AST LOG: parameter "shared_buffers" cannot be changed without restarting the server .. > 2014-07-03 08:09:40 AST LOG: configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected changes were applied > I predict this is going to be a huge source of confusion for new users, and at the very least the error message needs to be > a) correct >and b) actually tell the user the remedy. As I suggested previously, we can explain the same in the document as the above message can come in many ways, so changing that is not appropriate. I am thinking to add text somewhat like below in Alter System docs: "It is advisable to comment values changed by via Alter System in postgresql.conf else it can show spurious warnings during reload for parameters that can only be applied when the server starts." With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
>"/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected
the reason is that :
some config parameter changed need restart the pg server.
so when you reload ,there may be this error info
the reason is that :
some config parameter changed need restart the pg server.
so when you reload ,there may be this error info
At 2014-07-03 05:16:56, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> wrote: >Hi all, playing with 9.4 beta: > >Consider: > >$ cat config.sql >ALTER SYSTEM SET max_connections = 200; >ALTER SYSTEM SET shared_buffers = "10GB"; >ALTER SYSTEM SET maintenance_work_mem = "1GB"; >ALTER SYSTEM SET effective_io_concurrency = 10; >ALTER SYSTEM SET wal_buffers = "32MB"; >ALTER SYSTEM SET checkpoint_segments = 192; >ALTER SYSTEM SET checkpoint_completion_target = 0.8; > >$ psql < config.sql > >$ service postgresql restart > >$ psql >ALTER SYSTEM SET checkpoint_segments = 384; >\q > >$ service postgresql reload > >$ tail /var/log/postgresql/postgresql-9.4-main.log >2014-07-03 08:09:40 AST LOG: received SIGHUP, reloading configuration files >2014-07-03 08:09:40 AST LOG: parameter "max_connections" cannot be >changed without restarting the server >2014-07-03 08:09:40 AST LOG: parameter "shared_buffers" cannot be >changed without restarting the server >2014-07-03 08:09:40 AST LOG: parameter "checkpoint_segments" changed to >"1920" >2014-07-03 08:09:40 AST LOG: configuration file >"/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected >changes were applied > > >Hmmm - I didn't actually change the settings it is complaining >about...and it is referring to the wrong file too. While this is hardly >serious, it is likely to be immensely confusing for folk who have not >been actively following the new changes for 9.4 (ahem...that could be a >lot of people). It would be good to fix this before we end beta. > >Cheers > >Mark > > >-- >Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-bugs
On 04/07/14 15:11, Amit Kapila wrote: > I am thinking to add text somewhat like below in Alter System docs: > "It is advisable to comment values changed by via Alter System in > postgresql.conf else it can show spurious warnings during reload > for parameters that can only be applied when the server starts." Yes, I think that would be good - probably essential in fact. Regards Mark
On 04/07/14 15:11, Amit Kapila wrote: > > True, but as I explained in that thread as well that this is an > existing behaviour which is also visible via Alter System, so > changing an existing mechanism requires non-trivial fix (eliminating > duplicate entries during processing of config file) which I think > can be risky doing at this time in terms of impact, however we > can try to minimise the inconvenience user can face due to this feature. > However the existence of ALTER SYSTEM has made the likelihood of this error much more likely, and also more confusing. Previously users *knew* they were editing an additional include conf file or similar so the advice 'eliminate clashes' was fine - the new functionality hides this (which is great) but obfuscates the error and makes the error message wrong (not so good). So some additional clarification is really required. I have just had a similar conversation with some people here about ALTER SYSTEM in 9.4 - they *loved* the feature, but were bemused (at the very least) by the error message. Regards Mark
On Fri, Jul 4, 2014 at 11:24 AM, Mark Kirkwood < mark.kirkwood@catalyst.net.nz> wrote: > On 04/07/14 15:11, Amit Kapila wrote: >> True, but as I explained in that thread as well that this is an >> existing behaviour which is also visible via Alter System, so >> changing an existing mechanism requires non-trivial fix (eliminating >> duplicate entries during processing of config file) which I think >> can be risky doing at this time in terms of impact, however we >> can try to minimise the inconvenience user can face due to this feature. >> > > However the existence of ALTER SYSTEM has made the likelihood of this error much more likely, and also more confusing. Previously users *knew* they were editing an additional include conf file or similar so the advice 'eliminate clashes' was fine - the new functionality hides this (which is great) but obfuscates the error and makes the error message wrong (not so good). So some additional clarification is really required. Sure, as suggested upthread, I think we can add a note so that users can atleast aware of such a behaviour for now and for 9.5, I will try to see if I can eliminate this problem. > I have just had a similar conversation with some people here about ALTER SYSTEM in 9.4 - they *loved* the feature, but were bemused (at the very least) by the error message. Good to hear. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com