Обсуждение: timezone GUC
If you have the timezone configured to a non-default value in
postgresql.conf, and you comment it out and reload, it says:
LOG: parameter "TimeZone" removed from configuration file, reset to default
...but at least when I tested it, it didn't actually appear to reset
it to the default.
assign_timezone() has this to say:
/* * UNKNOWN is the value shown as the "default" for TimeZone in * guc.c. We
interpretit as being a complete no-op; we don't * change the timezone setting. Note that if there is a
known * timezone setting, we will return that name rather than UNKNOWN * as the canonical
spelling. * * During GUC initialization, since the timezone library isn't set * up yet,
pg_get_timezone_namewill return NULL and we will leave * the setting as UNKNOWN. If this isn't overridden
fromthe * config file then pg_timezone_initialize() will eventually * select a default value from
theenvironment. */
...but that seems a few bricks short of a load, because it doesn't
handle this case properly.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> If you have the timezone configured to a non-default value in
> postgresql.conf, and you comment it out and reload, it says:
> LOG: parameter "TimeZone" removed from configuration file, reset to default
> ...but at least when I tested it, it didn't actually appear to reset
> it to the default.
Hm, interesting. guc-file.l thinks this will fix it in such cases:
/* Now we can re-apply the wired-in default */ set_config_option(gconf->name, NULL, context, PGC_S_DEFAULT,
GUC_ACTION_SET, true);
but for variables where the powerup default is "do nothing just yet",
that, um, does nothing just yet. The patch I just applied doesn't
change this behavior. I suspect that this "re-apply" logic also fails
for cases where the intended default derives from environment variables.
Making this work as expected actually looks a bit nasty, because in the
case where the config file entry was there at system bootup, we never
did compute a state corresponding to its not being there. So it's not
just a matter of rolling back to some prior state.
regards, tom lane
On Thu, Apr 7, 2011 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> If you have the timezone configured to a non-default value in >> postgresql.conf, and you comment it out and reload, it says: > >> LOG: parameter "TimeZone" removed from configuration file, reset to default > >> ...but at least when I tested it, it didn't actually appear to reset >> it to the default. > > Hm, interesting. guc-file.l thinks this will fix it in such cases: > > /* Now we can re-apply the wired-in default */ > set_config_option(gconf->name, NULL, context, PGC_S_DEFAULT, > GUC_ACTION_SET, true); > > but for variables where the powerup default is "do nothing just yet", > that, um, does nothing just yet. The patch I just applied doesn't > change this behavior. I suspect that this "re-apply" logic also fails > for cases where the intended default derives from environment variables. > > Making this work as expected actually looks a bit nasty, because in the > case where the config file entry was there at system bootup, we never > did compute a state corresponding to its not being there. So it's not > just a matter of rolling back to some prior state. So I think you whacked this around some more to get a *somewhat* more sensible behavior, but ISTM that the behavior here still not really right. Should we select a timezone at startup time even if we don't immediately need it, so that this can work correctly if we revert to the default down the road? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> So I think you whacked this around some more to get a *somewhat* more
> sensible behavior, but ISTM that the behavior here still not really
> right. Should we select a timezone at startup time even if we don't
> immediately need it, so that this can work correctly if we revert to
> the default down the road?
We could do that, but the issue that this code is trying to avoid is
that identify_system_timezone can easily add several seconds to the
postmaster startup time. So one of the reasons why someone would be
putting a timezone setting into postgresql.conf in the first place is
to not pay that startup overhead. I'm not thrilled with the idea
of defeating that in order to cover the corner case where they remove
the setting later.
regards, tom lane
On Thu, May 19, 2011 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> So I think you whacked this around some more to get a *somewhat* more >> sensible behavior, but ISTM that the behavior here still not really >> right. Should we select a timezone at startup time even if we don't >> immediately need it, so that this can work correctly if we revert to >> the default down the road? > > We could do that, but the issue that this code is trying to avoid is > that identify_system_timezone can easily add several seconds to the > postmaster startup time. So one of the reasons why someone would be > putting a timezone setting into postgresql.conf in the first place is > to not pay that startup overhead. I'm not thrilled with the idea > of defeating that in order to cover the corner case where they remove > the setting later. Yeah, I'm not thrilled with that either. However, I'm also not thrilled with the existing behavior. Consistency is the hobgoblin of small minds, and also mine. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 19, 2011 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We could do that, but the issue that this code is trying to avoid is
>> that identify_system_timezone can easily add several seconds to the
>> postmaster startup time. �So one of the reasons why someone would be
>> putting a timezone setting into postgresql.conf in the first place is
>> to not pay that startup overhead. �I'm not thrilled with the idea
>> of defeating that in order to cover the corner case where they remove
>> the setting later.
> Yeah, I'm not thrilled with that either. However, I'm also not
> thrilled with the existing behavior. Consistency is the hobgoblin of
> small minds, and also mine.
What would make everybody happy is to find a way of identifying the
system timezone that isn't such a massive, expensive kluge. Any ideas?
regards, tom lane
On Thu, May 19, 2011 at 12:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, May 19, 2011 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> We could do that, but the issue that this code is trying to avoid is >>> that identify_system_timezone can easily add several seconds to the >>> postmaster startup time. So one of the reasons why someone would be >>> putting a timezone setting into postgresql.conf in the first place is >>> to not pay that startup overhead. I'm not thrilled with the idea >>> of defeating that in order to cover the corner case where they remove >>> the setting later. > >> Yeah, I'm not thrilled with that either. However, I'm also not >> thrilled with the existing behavior. Consistency is the hobgoblin of >> small minds, and also mine. > > What would make everybody happy is to find a way of identifying the > system timezone that isn't such a massive, expensive kluge. Any ideas? ...reads the code... Good grief. What an incredibly ugly hack. Is there seriously no portable way of doing this? If not, then how about jiggering things somehow so that only one server process needs to run the hack? Perhaps it can drop the result in a file or shared memory or something from which the remaining backends can read it out without having to redo all that work? Admittedly there is a synchronization problem there I'm not quite sure how to solve. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 19, 2011 at 12:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What would make everybody happy is to find a way of identifying the
>> system timezone that isn't such a massive, expensive kluge. Any ideas?
> ...reads the code...
> Good grief. What an incredibly ugly hack. Is there seriously no
> portable way of doing this?
AFAIK, not. The info is certainly not available in the POSIX/SUS spec,
probably because they don't think there's a standardized idea of what
timezone names are to begin with.
(There was some discussion just a week or two ago in the Fedora lists
about this, but I fear Lennart Poettering suffers from a seriously
inflated view of his ability to establish standards without bothering
with any actual, um, standards process.)
> If not, then how about jiggering things somehow so that only one
> server process needs to run the hack? Perhaps it can drop the result
> in a file or shared memory or something from which the remaining
> backends can read it out without having to redo all that work?
> Admittedly there is a synchronization problem there I'm not quite sure
> how to solve.
Well, the main point in my mind is that the process is so damn expensive
that we don't want to run it at all, ever, if we can avoid it.
We could imagine launching a postmaster child to compute the result,
as you suggest. And it would work 99.9% of the time, because probably
nobody would remove the setting from postgresql.conf within a few
seconds of having started the postmaster. But also, 99.999% of the time
it would be completely wasted effort because the DBA wouldn't remove the
postgresql.conf setting at all, ever.
regards, tom lane
On Sun, May 22, 2011 at 9:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> If not, then how about jiggering things somehow so that only one >> server process needs to run the hack? Perhaps it can drop the result >> in a file or shared memory or something from which the remaining >> backends can read it out without having to redo all that work? >> Admittedly there is a synchronization problem there I'm not quite sure >> how to solve. > > Well, the main point in my mind is that the process is so damn expensive > that we don't want to run it at all, ever, if we can avoid it. > > We could imagine launching a postmaster child to compute the result, > as you suggest. And it would work 99.9% of the time, because probably > nobody would remove the setting from postgresql.conf within a few > seconds of having started the postmaster. But also, 99.999% of the time > it would be completely wasted effort because the DBA wouldn't remove the > postgresql.conf setting at all, ever. Well, by that argument, we ought not to worry about masterminding what happens if the DBA does do such a thing -- just run the whole process and damn the torpedoes. If it causes a brief database stall, at least they'll get the correct behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, May 22, 2011 at 9:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But also, 99.999% of the time
>> it would be completely wasted effort because the DBA wouldn't remove the
>> postgresql.conf setting at all, ever.
> Well, by that argument, we ought not to worry about masterminding what
> happens if the DBA does do such a thing -- just run the whole process
> and damn the torpedoes. If it causes a brief database stall, at least
> they'll get the correct behavior.
Yeah, maybe. But I don't especially want to document "If you remove a
pre-existing setting of TimeZone from postgresql.conf, expect your
database to lock up hard for multiple seconds" ... and I think we
couldn't responsibly avoid mentioning it. At the moment that disclaimer
reads more like "If you remove a pre-existing setting of TimeZone from
postgresql.conf, the database will fall back to a default that might not
be what you were expecting". Is A really better than B?
regards, tom lane
On Sun, May 22, 2011 at 10:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sun, May 22, 2011 at 9:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> But also, 99.999% of the time >>> it would be completely wasted effort because the DBA wouldn't remove the >>> postgresql.conf setting at all, ever. > >> Well, by that argument, we ought not to worry about masterminding what >> happens if the DBA does do such a thing -- just run the whole process >> and damn the torpedoes. If it causes a brief database stall, at least >> they'll get the correct behavior. > > Yeah, maybe. But I don't especially want to document "If you remove a > pre-existing setting of TimeZone from postgresql.conf, expect your > database to lock up hard for multiple seconds" ... and I think we > couldn't responsibly avoid mentioning it. At the moment that disclaimer > reads more like "If you remove a pre-existing setting of TimeZone from > postgresql.conf, the database will fall back to a default that might not > be what you were expecting". Is A really better than B? Well, I'm not entirely sure, but I lean toward yes. Anyone else have an opinion? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of dom may 22 23:09:47 -0400 2011: > On Sun, May 22, 2011 at 10:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Robert Haas <robertmhaas@gmail.com> writes: > >> On Sun, May 22, 2011 at 9:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >>> But also, 99.999% of the time > >>> it would be completely wasted effort because the DBA wouldn't remove the > >>> postgresql.conf setting at all, ever. > > > >> Well, by that argument, we ought not to worry about masterminding what > >> happens if the DBA does do such a thing -- just run the whole process > >> and damn the torpedoes. If it causes a brief database stall, at least > >> they'll get the correct behavior. > > > > Yeah, maybe. But I don't especially want to document "If you remove a > > pre-existing setting of TimeZone from postgresql.conf, expect your > > database to lock up hard for multiple seconds" ... and I think we > > couldn't responsibly avoid mentioning it. At the moment that disclaimer > > reads more like "If you remove a pre-existing setting of TimeZone from > > postgresql.conf, the database will fall back to a default that might not > > be what you were expecting". Is A really better than B? > > Well, I'm not entirely sure, but I lean toward yes. Anyone else have > an opinion? Yes, I think the lock-up is better than weird behavior. Maybe we should add a short note in a postgresql.conf comment to this effect, so that it doesn't surprise anyone that deletes or comments out the line. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On May 26, 2011, at 12:41 PM, Alvaro Herrera wrote: > Yes, I think the lock-up is better than weird behavior. Maybe we should > add a short note in a postgresql.conf comment to this effect, so that it > doesn't surprise anyone that deletes or comments out the line. +1 on both counts. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > If you have the timezone configured to a non-default value in > > postgresql.conf, and you comment it out and reload, it says: > > > LOG: parameter "TimeZone" removed from configuration file, reset to default > > > ...but at least when I tested it, it didn't actually appear to reset > > it to the default. > > Hm, interesting. guc-file.l thinks this will fix it in such cases: > > /* Now we can re-apply the wired-in default */ > set_config_option(gconf->name, NULL, context, PGC_S_DEFAULT, > GUC_ACTION_SET, true); > > but for variables where the powerup default is "do nothing just yet", > that, um, does nothing just yet. The patch I just applied doesn't > change this behavior. I suspect that this "re-apply" logic also fails > for cases where the intended default derives from environment variables. > > Making this work as expected actually looks a bit nasty, because in the > case where the config file entry was there at system bootup, we never > did compute a state corresponding to its not being there. So it's not > just a matter of rolling back to some prior state. Was this dealt with? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> Was this dealt with?
Yes, as much as I think is reasonable anyway. See
commit e05b866447899211a0c2df31bf0671faac4fc3e5.
regards, tom lane
On Tue, Sep 6, 2011 at 3:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: >> Was this dealt with? > > Yes, as much as I think is reasonable anyway. See > commit e05b866447899211a0c2df31bf0671faac4fc3e5. I am (and, I think, Alvaro is also) of the opinion that the behavior here is still not really right. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Sep 6, 2011 at 3:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> Was this dealt with?
>> Yes, as much as I think is reasonable anyway. �See
>> commit e05b866447899211a0c2df31bf0671faac4fc3e5.
> I am (and, I think, Alvaro is also) of the opinion that the behavior
> here is still not really right.
I don't see a practical way to do better unless we can find a less
horridly inefficient way of implementing identify_system_timezone().
Unfortunately, it's unlikely that there is one, even if you're willing
to make the simplifying assumption that the system timezone is based
on some entry in the Olson database.
regards, tom lane
I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I am (and, I think, Alvaro is also) of the opinion that the behavior
>> here is still not really right.
> I don't see a practical way to do better unless we can find a less
> horridly inefficient way of implementing identify_system_timezone().
Although there's always more than one way to skin a cat. Consider
this idea:
1. The hard-wired default for timezone is always UTC (or something
else not dependent on environment).
2. We put the identify_system_timezone work into initdb, and have it
inject a non-default entry into postgresql.conf in the usual way
if it can identify what the system zone is.
3. Run-time dependency on TZ environment disappears altogether.
This basically means that instead of incurring that search on every
postmaster start, we do it once at initdb. If you change the
postmaster's timezone environment, well, you gotta go change
postgresql.conf.
IMO this would be less DBA-friendly in practice, but only very
marginally so; and getting rid of the special initialization behavior of
the timezone GUC might well be considered sufficient recompense.
regards, tom lane
On Tue, Sep 6, 2011 at 5:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> I am (and, I think, Alvaro is also) of the opinion that the behavior >>> here is still not really right. > >> I don't see a practical way to do better unless we can find a less >> horridly inefficient way of implementing identify_system_timezone(). > > Although there's always more than one way to skin a cat. Consider > this idea: > > 1. The hard-wired default for timezone is always UTC (or something > else not dependent on environment). > > 2. We put the identify_system_timezone work into initdb, and have it > inject a non-default entry into postgresql.conf in the usual way > if it can identify what the system zone is. > > 3. Run-time dependency on TZ environment disappears altogether. > > This basically means that instead of incurring that search on every > postmaster start, we do it once at initdb. If you change the > postmaster's timezone environment, well, you gotta go change > postgresql.conf. > > IMO this would be less DBA-friendly in practice, but only very > marginally so; and getting rid of the special initialization behavior of > the timezone GUC might well be considered sufficient recompense. Seems reasonable to me... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Sep 6, 2011 at 23:52, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Sep 6, 2011 at 5:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I wrote: >>> Robert Haas <robertmhaas@gmail.com> writes: >>>> I am (and, I think, Alvaro is also) of the opinion that the behavior >>>> here is still not really right. >> >>> I don't see a practical way to do better unless we can find a less >>> horridly inefficient way of implementing identify_system_timezone(). >> >> Although there's always more than one way to skin a cat. Consider >> this idea: >> >> 1. The hard-wired default for timezone is always UTC (or something >> else not dependent on environment). >> >> 2. We put the identify_system_timezone work into initdb, and have it >> inject a non-default entry into postgresql.conf in the usual way >> if it can identify what the system zone is. >> >> 3. Run-time dependency on TZ environment disappears altogether. >> >> This basically means that instead of incurring that search on every >> postmaster start, we do it once at initdb. If you change the >> postmaster's timezone environment, well, you gotta go change >> postgresql.conf. >> >> IMO this would be less DBA-friendly in practice, but only very >> marginally so; and getting rid of the special initialization behavior of >> the timezone GUC might well be considered sufficient recompense. > > Seems reasonable to me... +1. I'm not sure I agree it's less DBA-friendly, really - given that it makes it more consistent.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> On Tue, Sep 6, 2011 at 23:52, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Tue, Sep 6, 2011 at 5:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Although there's always more than one way to skin a cat. �Consider
>>> this idea:
>>>
>>> 1. The hard-wired default for timezone is always UTC (or something
>>> else not dependent on environment).
>>>
>>> 2. We put the identify_system_timezone work into initdb, and have it
>>> inject a non-default entry into postgresql.conf in the usual way
>>> if it can identify what the system zone is.
>>>
>>> 3. Run-time dependency on TZ environment disappears altogether.
>>>
>>> This basically means that instead of incurring that search on every
>>> postmaster start, we do it once at initdb. �If you change the
>>> postmaster's timezone environment, well, you gotta go change
>>> postgresql.conf.
>> Seems reasonable to me...
> +1.
I spent a bit of time on this idea last night. The most painful part
actually seems to be translating identify_system_timezone to run in a
non-backend environment (no elog, etc). The one thing I've run into
that doesn't seem straightforward is to decide where to look for the
timezone files. If we have --with-system-tzdata then of course it's a
constant, but should we honor initdb's -L switch otherwise? And if so,
how should we pass that into the pg_TZDIR code?
regards, tom lane
On ons, 2011-09-07 at 17:16 -0400, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > > On Tue, Sep 6, 2011 at 23:52, Robert Haas <robertmhaas@gmail.com> wrote: > >> On Tue, Sep 6, 2011 at 5:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >>> Although there's always more than one way to skin a cat. Consider > >>> this idea: > >>> > >>> 1. The hard-wired default for timezone is always UTC (or something > >>> else not dependent on environment). > >>> > >>> 2. We put the identify_system_timezone work into initdb, and have it > >>> inject a non-default entry into postgresql.conf in the usual way > >>> if it can identify what the system zone is. > >>> > >>> 3. Run-time dependency on TZ environment disappears altogether. > >>> > >>> This basically means that instead of incurring that search on every > >>> postmaster start, we do it once at initdb. If you change the > >>> postmaster's timezone environment, well, you gotta go change > >>> postgresql.conf. > > >> Seems reasonable to me... > > > +1. > > I spent a bit of time on this idea last night. The most painful part > actually seems to be translating identify_system_timezone to run in a > non-backend environment (no elog, etc). The one thing I've run into > that doesn't seem straightforward is to decide where to look for the > timezone files. If we have --with-system-tzdata then of course it's a > constant, but should we honor initdb's -L switch otherwise? And if so, > how should we pass that into the pg_TZDIR code? > > regards, tom lane > It looks like the --with-system-tzdata case is somewhat broken now in initdb: creating configuration files ... could not open directory "...../pg-install/share/timezone": No such file or directory ok
Peter Eisentraut <peter_e@gmx.net> writes:
> It looks like the --with-system-tzdata case is somewhat broken now in
> initdb:
> creating configuration files ... could not open directory "...../pg-install/share/timezone": No such file or
directory
Sigh. That's what I get for assuming that case was simple enough to not
need testing. Will fix. Where's my brown paper bag?
regards, tom lane