Thread: logging settings
On Postgres 7.4.1 I've been experiencing some strangeness with the logging settings in postgresql.conf. I turned on statement logging (and duration), issued a "pg_ctl reload" and got it working (logging to sys log and standard out). Now however, when i try to turn it off, it won't turn off, using the same procedure. I've now commented out all the lines related to logging, but it's still logging. Is it possible that this is related to connection pooling? Does each connection hold these settings, such that a reload of the configuration file only affects new connections, not existing ones? Any help, much appreciated. Thanks - Mott
On Thu, Feb 02, 2006 at 06:19:53PM -0500, Mott Leroy wrote: > On Postgres 7.4.1 That's pretty old. If you can't upgrade to 8.0 or 8.1 then at least consider staying up to date with bug fixes by using the latest version in the 7.4 branch (currently 7.4.11). > I've been experiencing some strangeness with the logging settings in > postgresql.conf. > > I turned on statement logging (and duration), issued a "pg_ctl reload" > and got it working (logging to sys log and standard out). Now however, > when i try to turn it off, it won't turn off, using the same procedure. > I've now commented out all the lines related to logging, but it's still > logging. Did you change the settings to off and leave them uncommented or did you simply comment them out? Commenting out the lines will leave the settings at their previous values until you restart the server; if you want to change settings with a reload (not a restart) then change the values and leave them uncommented. Once you've actively changed the settings back to their defaults then you could comment them out. > Is it possible that this is related to connection pooling? Does each > connection hold these settings, such that a reload of the configuration > file only affects new connections, not existing ones? I just did some tests in 7.4.11 and settings like log_statement and log_min_duration_statement changed within a session as I reloaded the server, so I don't think connection pooling would matter (at least not for those settings). I think the more likely problem is that you commented out the settings instead of actually changing them. -- Michael Fuhr
Mott Leroy <mott@acadaca.com> writes:
> I turned on statement logging (and duration), issued a "pg_ctl reload"
> and got it working (logging to sys log and standard out). Now however,
> when i try to turn it off, it won't turn off, using the same procedure.
> I've now commented out all the lines related to logging, but it's still
> logging.
I think you're getting bit by a standard beginner gotcha: commenting out
an entry in postgresql.conf will not change the state of a running
postmaster. (A comment is a no-op, eh?) You need to put in a
non-comment entry that sets the desired state.
There's been various discussions in the past about making this behavior
less non-intuitive, but nothing's been settled on ...
regards, tom lane
Tom Lane wrote: > I think you're getting bit by a standard beginner gotcha: commenting out > an entry in postgresql.conf will not change the state of a running > postmaster. (A comment is a no-op, eh?) You need to put in a > non-comment entry that sets the desired state. This was indeed the case. I thought I had tried placing the values back without commenting the lines out, but apparently not. Thanks for your help! - Mott
On Fri, Feb 03, 2006 at 12:13:15AM -0500, Tom Lane wrote: > I think you're getting bit by a standard beginner gotcha: commenting out > an entry in postgresql.conf will not change the state of a running > postmaster. (A comment is a no-op, eh?) You need to put in a > non-comment entry that sets the desired state. > There's been various discussions in the past about making this behavior > less non-intuitive, but nothing's been settled on ... So what about deciding now that it should be changed. What are the reasons for why it should be kept as it is? I've found "pg_ctl reports succes when start fails" in the archives but without someone voting against changing it. Does anybody have better pointers? I propose to change it on the grounds that: - other unix daemons reset their values to defaults before reading conffiles - lots of people find it unintuitive - after restarting pgsql you can never be sure that it runs with the same configuration as before even with an unchanged postgresql.conf The mtime of postgresql.conf then makes you think that it has been running fine with these options for the last 3 months so you don't even take a look at the conf file but look for the problem elsewhere... Joachim -- Joachim Wieland joe@mcknight.de C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available
On Sun, Feb 05, 2006 at 07:43:47PM +0100, Joachim Wieland wrote: > I propose to change it on the grounds that: > > - other unix daemons reset their values to defaults before reading > conffiles To be fair, most daemons can do this by simply re-execing themselves, something we don't have the luxury of. For most daemons, the presense of a line enables something which you can disable again. Most of our config values are always present, whether or not they're commented out. But the biggest issue is that not all values can be changed by reloading the config files. So for these variables, no amount of "making it more obvious" will change the fact that you're not running with the values it says in the config file. > - lots of people find it unintuitive But resetting to defaults on reload doesn't solve the problem due to the above issue. Also, once you know it's not that odd. > - after restarting pgsql you can never be sure that it runs with > the same configuration as before even with an unchanged postgresql.conf > > The mtime of postgresql.conf then makes you think that it has > been running fine with these options for the last 3 months so you don't > even take a look at the conf file but look for the problem elsewhere... I'm sure you could find lots of people to go for a change, it's just the mechanism that people don't agree on. What do you think should happen to unchangable config values? You can always see what the current values are using SHOW ALL. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Joachim Wieland <joe@mcknight.de> writes:
>> There's been various discussions in the past about making this behavior
>> less non-intuitive, but nothing's been settled on ...
> So what about deciding now that it should be changed. What are the reasons
> for why it should be kept as it is?
1. Backwards compatibility.
2. A comment is a comment.
> - other unix daemons reset their values to defaults before reading
> conffiles
Examples please?
regards, tom lane
On Sun, Feb 05, 2006 at 07:59:06PM +0100, Martijn van Oosterhout wrote:
> On Sun, Feb 05, 2006 at 07:43:47PM +0100, Joachim Wieland wrote:
> > I propose to change it on the grounds that:
> > - other unix daemons reset their values to defaults before reading
> > conffiles
> But the biggest issue is that not all values can be changed by
> reloading the config files. So for these variables, no amount of
> "making it more obvious" will change the fact that you're not running
> with the values it says in the config file.
Sure, but theres another item on the TODO list...
errmmm, actually it's done:
o -Issue a warning if a change-on-restart-only postgresql.conf value
is modified and the server config files are reloaded
I just notice that Peter has done this yesterday.
> > - lots of people find it unintuitive
> But resetting to defaults on reload doesn't solve the problem due to
> the above issue. Also, once you know it's not that odd.
No, but setting back to defaults commented variables and issuing a note
for those where it is not possible to change the actual value seems to be a
clean solution.
> > - after restarting pgsql you can never be sure that it runs with
> > the same configuration as before even with an unchanged postgresql.conf
> > The mtime of postgresql.conf then makes you think that it has
> > been running fine with these options for the last 3 months so you don't
> > even take a look at the conf file but look for the problem elsewhere...
> I'm sure you could find lots of people to go for a change, it's just
> the mechanism that people don't agree on. What do you think should
> happen to unchangable config values? You can always see what the
> current values are using SHOW ALL.
Yes but I doubt that people like the idea that they have to cross-check SHOW
ALL output with postgresql.conf after reload just to make sure that the
database will come up the next time as it should.
Joachim
--
Joachim Wieland joe@mcknight.de
C/ Usandizaga 12 1°B ICQ: 37225940
20002 Donostia / San Sebastian (Spain) GPG key available
On Sun, Feb 05, 2006 at 02:37:53PM -0500, Tom Lane wrote: > Joachim Wieland <joe@mcknight.de> writes: > >> There's been various discussions in the past about making this behavior > >> less non-intuitive, but nothing's been settled on ... > > So what about deciding now that it should be changed. What are the reasons > > for why it should be kept as it is? > 1. Backwards compatibility. Since you have to restart for a new version anyway and since I doubt that there are many tools around that change settings automatically and rely on today's behavior, the backwards compatibility problem only exists in the admin's head who either doesn't know about the problem at all or will read about it in the release notes before installing the new version. > 2. A comment is a comment. True, that's why a setting that gets commented is not in effect anymore and does not override the default setting for the respective option. If a comment is a comment, why should the server continue to run with the setting of a comment? You can also delete the setting, then the server runs with a setting that isn't documented in any place. > > - other unix daemons reset their values to defaults before reading > > conffiles > Examples please? inetd, Apache, squid, exim, postfix, a famous database starting with m... Joachim -- Joachim Wieland joe@mcknight.de C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available
Joachim Wieland wrote: > > > - other unix daemons reset their values to defaults before > > > reading conffiles > > > > Examples please? > > inetd, Apache, squid, exim, postfix, a famous database starting with > m... cron, syslog, ntp, ssh -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Sun, Feb 05, 2006 at 09:56:17PM +0100, Peter Eisentraut wrote: > > inetd, Apache, squid, exim, postfix, a famous database starting with > > m... > > cron, syslog, ntp, ssh All of those work either by re-execing themselves, if they implement a reload at all. Except mysql which only appears to allow reloading "grant tables". I'm a bit surprised about that, the docs clearly talk about reloading grant tables, they don't say anything about reloading the configuration. Anyone know enough about mysql to set the record straight here? Searching the docs didn't bring me any joy. How about oracle and such? Anyway, the behviour I think people are looking for is that when you comment out a line, it resets the value back to some default, which they'll find in the documentation I presume. Spit out a warning for things that can't be changed (this appears to be done). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
TODO has:
o %Allow commenting of variables in postgresql.conf to restore them
to defaults
Currently, if a variable is commented out, it keeps the
previous uncommented value until a server restarted.
---------------------------------------------------------------------------
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Sun, Feb 05, 2006 at 09:56:17PM +0100, Peter Eisentraut wrote:
> > > inetd, Apache, squid, exim, postfix, a famous database starting with
> > > m...
> >
> > cron, syslog, ntp, ssh
>
> All of those work either by re-execing themselves, if they implement a
> reload at all. Except mysql which only appears to allow reloading
> "grant tables". I'm a bit surprised about that, the docs clearly talk
> about reloading grant tables, they don't say anything about reloading
> the configuration. Anyone know enough about mysql to set the record
> straight here? Searching the docs didn't bring me any joy.
>
> How about oracle and such?
>
> Anyway, the behviour I think people are looking for is that when you
> comment out a line, it resets the value back to some default, which
> they'll find in the documentation I presume. Spit out a warning for
> things that can't be changed (this appears to be done).
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073