Thread: Correction of how to use TimeZone by ControlFile(xlog.c)
Hi. (sorry, this mail is UTF8) This problem is remarkable in the Japanese windows environment.... It appears in a log output....See, - LOG: database system was shut down at 2007-07-31 19:23:25 東京 (標準時) LOG: database system is ready to accept connections LOG: autovacuum launcher started (This appears as SJIS in encoding of client in fact. Not server encoding..) So, It changes strikingly after patch.! - LOG: database system was shut down at 2007-08-01 10:15:12 JST LOG: database system is ready to accept connections LOG: autovacuum launcher started This is correctly treated by the timezone library with which PostgreSQL was equipped. Please take this into consideration. Thanks! Regards, Hiroshi Saito
Attachment
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> [ patch to use pg_strftime in xlog.c ]
This code deliberately does not use pg_strftime, for the same reasons
that elog.c doesn't use it.
I'm inclined to think that an appropriate fix is the same as we use in
elog.c, ie, don't use %Z at all under Windows.
regards, tom lane
Hi Tom-san. From: "Tom Lane" > "Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: >> [ patch to use pg_strftime in xlog.c ] > > This code deliberately does not use pg_strftime, for the same reasons > that elog.c doesn't use it. > > I'm inclined to think that an appropriate fix is the same as we use in > elog.c, ie, don't use %Z at all under Windows. Eh? Do you mean this change? "%Y-%m-%d %H:%M:%S %Z" to "%Y-%m-%d %H:%M:%S" That tzname is expressed here does not regard me as a problem. Probably, elog.c has still more nearly another problem. Regards, Hiroshi Saito
On Wed, Aug 01, 2007 at 11:17:21AM +0900, Hiroshi Saito wrote: > >"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: > >>[ patch to use pg_strftime in xlog.c ] > > > >This code deliberately does not use pg_strftime, for the same reasons > >that elog.c doesn't use it. > > > >I'm inclined to think that an appropriate fix is the same as we use in > >elog.c, ie, don't use %Z at all under Windows. > > Eh? Do you mean this change? > "%Y-%m-%d %H:%M:%S %Z" to "%Y-%m-%d %H:%M:%S" > That tzname is expressed here does not regard me as a problem. > Probably, elog.c has still more nearly another problem. Having talked a bit off-list with Hiroshi-san, he came up with the suggestion taht we should be logging this information in UTC/GMT instead of the servers timezone (for all cases, not just win32). That would make things equally "safe" wrt changes in the pg timezone, and always predictable. Thoughts on this? //Magnus
Magnus Hagander wrote: > On Wed, Aug 01, 2007 at 11:17:21AM +0900, Hiroshi Saito wrote: > >>> "Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: >>> >>>> [ patch to use pg_strftime in xlog.c ] >>>> >>> This code deliberately does not use pg_strftime, for the same reasons >>> that elog.c doesn't use it. >>> >>> I'm inclined to think that an appropriate fix is the same as we use in >>> elog.c, ie, don't use %Z at all under Windows. >>> >> Eh? Do you mean this change? >> "%Y-%m-%d %H:%M:%S %Z" to "%Y-%m-%d %H:%M:%S" >> That tzname is expressed here does not regard me as a problem. >> Probably, elog.c has still more nearly another problem. >> > > Having talked a bit off-list with Hiroshi-san, he came up with the > suggestion taht we should be logging this information in UTC/GMT instead of > the servers timezone (for all cases, not just win32). That would make > things equally "safe" wrt changes in the pg timezone, and always > predictable. > > Thoughts on this? > > > Are you just talking about xlog.c? That wouldn't be an acceptable change in elog.c, although we could provide additional escapes to output UTC if needed. cheers andrew
On Fri, Aug 03, 2007 at 09:01:22AM -0400, Andrew Dunstan wrote: > >>>>[ patch to use pg_strftime in xlog.c ] > >>>> > >>>This code deliberately does not use pg_strftime, for the same reasons > >>>that elog.c doesn't use it. > >>> > >>>I'm inclined to think that an appropriate fix is the same as we use in > >>>elog.c, ie, don't use %Z at all under Windows. > >>> > >>Eh? Do you mean this change? > >>"%Y-%m-%d %H:%M:%S %Z" to "%Y-%m-%d %H:%M:%S" > >>That tzname is expressed here does not regard me as a problem. > >>Probably, elog.c has still more nearly another problem. > >> > > > >Having talked a bit off-list with Hiroshi-san, he came up with the > >suggestion taht we should be logging this information in UTC/GMT instead of > >the servers timezone (for all cases, not just win32). That would make > >things equally "safe" wrt changes in the pg timezone, and always > >predictable. > > > >Thoughts on this? > > > > > > > > Are you just talking about xlog.c? That wouldn't be an acceptable change > in elog.c, although we could provide additional escapes to output UTC if > needed. Yes, just talking about xlog.c. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> On Fri, Aug 03, 2007 at 09:01:22AM -0400, Andrew Dunstan wrote:
>>> Having talked a bit off-list with Hiroshi-san, he came up with the
>>> suggestion taht we should be logging this information in UTC/GMT instead of
>>> the servers timezone (for all cases, not just win32).
>> Are you just talking about xlog.c? That wouldn't be an acceptable change
>> in elog.c, although we could provide additional escapes to output UTC if
>> needed.
> Yes, just talking about xlog.c.
I don't think it's an acceptable change in either place. People who
want to see UTC in their logs can start the postmaster in UTC. Those
who are accustomed to seeing local time will squawk.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Magnus Hagander <magnus@hagander.net> writes: >> On Fri, Aug 03, 2007 at 09:01:22AM -0400, Andrew Dunstan wrote: >>>> Having talked a bit off-list with Hiroshi-san, he came up with the >>>> suggestion taht we should be logging this information in UTC/GMT instead of >>>> the servers timezone (for all cases, not just win32). > >>> Are you just talking about xlog.c? That wouldn't be an acceptable change >>> in elog.c, although we could provide additional escapes to output UTC if >>> needed. > >> Yes, just talking about xlog.c. > > I don't think it's an acceptable change in either place. People who > want to see UTC in their logs can start the postmaster in UTC. Those > who are accustomed to seeing local time will squawk. It would probably make sense to use UTC in the CSV logs since they're intended to be machine readable. Whatever program is used to read them can handle displaying the timestamps in the appropriate timezone for user's consumption. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> I don't think it's an acceptable change in either place. People who
>> want to see UTC in their logs can start the postmaster in UTC. Those
>> who are accustomed to seeing local time will squawk.
> It would probably make sense to use UTC in the CSV logs since they're intended
> to be machine readable. Whatever program is used to read them can handle
> displaying the timestamps in the appropriate timezone for user's consumption.
I don't think we really want to pay for formatting the timestamp twice
(the existing patch goes out of its way to avoid that IIRC).
Thinking back, the major reason why we use platform strftime() here is
to ensure that all backends will print log entries in the same timezone
regardless of session settings of the TimeZone GUC. That decision was
taken a long time ago, before we had the modern GUC infrastructure,
and also before we had pgtz infrastructure that could efficiently handle
conversions in multiple zones at once. Perhaps it would make sense to
invent a system-wide (PGC_SIGHUP) variable "log_timezone" and use
pg_strftime() with that setting to format timestamps for the log.
That would eliminate platform variability of all kinds, not just the
immediate Windows issue, and it might gain performance on some platforms
(glibc in particular has a bad habit of doing a syscall for every
strftime call). The only downside I can think of is that problems with
the log_timezone setting could lead to recursive errors and eventual
PANIC, but that's true of almost anything that goes wrong during error
printout.
Comments?
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> "Tom Lane" <tgl@sss.pgh.pa.us> writes: >>> I don't think it's an acceptable change in either place. People who >>> want to see UTC in their logs can start the postmaster in UTC. Those >>> who are accustomed to seeing local time will squawk. > >> It would probably make sense to use UTC in the CSV logs since they're intended >> to be machine readable. Whatever program is used to read them can handle >> displaying the timestamps in the appropriate timezone for user's consumption. > > I don't think we really want to pay for formatting the timestamp twice > (the existing patch goes out of its way to avoid that IIRC). Well even if we include the time in integer seconds-since-unix-epoch format it would be useful for a CSV data format. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Hi. From: "Tom Lane" <tgl@sss.pgh.pa.us> > Gregory Stark <stark@enterprisedb.com> writes: >> "Tom Lane" <tgl@sss.pgh.pa.us> writes: >>> I don't think it's an acceptable change in either place. People who >>> want to see UTC in their logs can start the postmaster in UTC. Those >>> who are accustomed to seeing local time will squawk. > >> It would probably make sense to use UTC in the CSV logs since they're intended >> to be machine readable. Whatever program is used to read them can handle >> displaying the timestamps in the appropriate timezone for user's consumption. > > I don't think we really want to pay for formatting the timestamp twice > (the existing patch goes out of its way to avoid that IIRC). About patch, it was only a thing for one solution. I thought that a next discussion was required for the problem which elog has. > > Thinking back, the major reason why we use platform strftime() here is > to ensure that all backends will print log entries in the same timezone > regardless of session settings of the TimeZone GUC. That decision was > taken a long time ago, before we had the modern GUC infrastructure, > and also before we had pgtz infrastructure that could efficiently handle > conversions in multiple zones at once. Perhaps it would make sense to > invent a system-wide (PGC_SIGHUP) variable "log_timezone" and use > pg_strftime() with that setting to format timestamps for the log. Ah yes, I agreed on the worries about the change. > > That would eliminate platform variability of all kinds, not just the > immediate Windows issue, and it might gain performance on some platforms > (glibc in particular has a bad habit of doing a syscall for every > strftime call). The only downside I can think of is that problems with > the log_timezone setting could lead to recursive errors and eventual > PANIC, but that's true of almost anything that goes wrong during error > printout. I understand the essence which you say. Then, I think that gmtime is an ideal there. localtime also takes summer time into consideration. It changes and sometimes falls unconsciously. Furthermore, a tzname can't be expressed by the present elog.... Moreover, how about relocating datacenter to New York from the loss Angelus? The log is continued.... database log is an audit item. Recently, it is more important than a response. And it is used secondarily. What does a CSV output become in the latest feature? I am not good at explanation. However, I desire what you understand. Regards, Hiroshi Saito
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> I understand the essence which you say. Then, I think that gmtime is an ideal
> there. localtime also takes summer time into consideration. It changes and
> sometimes falls unconsciously. Furthermore, a tzname can't be expressed
> by the present elog....
> Moreover, how about relocating datacenter to New York from the loss Angelus?
> The log is continued.... database log is an audit item. Recently, it is more
> important than a response. And it is used secondarily.
People who find the above arguments compelling would certainly be free
to set their log_timezone to GMT. Those who don't find them compelling
should not be forced to deal in GMT. The fact that Postgres has always
logged in system local time, and we've had no complaints about that,
suggests to me that most people prefer local-time logging.
regards, tom lane
> "Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: >> I understand the essence which you say. Then, I think that gmtime is an ideal >> there. localtime also takes summer time into consideration. It changes and >> sometimes falls unconsciously. Furthermore, a tzname can't be expressed >> by the present elog.... >> Moreover, how about relocating datacenter to New York from the loss Angelus? >> The log is continued.... database log is an audit item. Recently, it is more >> important than a response. And it is used secondarily. > > People who find the above arguments compelling would certainly be free > to set their log_timezone to GMT. Those who don't find them compelling > should not be forced to deal in GMT. The fact that Postgres has always > logged in system local time, and we've had no complaints about that, > suggests to me that most people prefer local-time logging. A certain user said. timezone of GUC is not effective with an elog output..... I am, though it is an ideal that there is (e)log_timezone which you say.:-) Um, I think that only for PostgreSQL is good. BTW, windows user should do how.? Regards, Hiroshi Saito
Gregory Stark wrote: > Well even if we include the time in integer seconds-since-unix-epoch format it > would be useful for a CSV data format. > > > That's probably the worst of all possible options. Two very common uses of CSVlogs will be a) to load them into a PostgreSQL table and b) to load them into a spreadsheet such as Excel. In both cases having a Unix epoch time rather than a timestamp is likely to be very annoying. Tom's idea of a log_timezone seems to make sense. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom's idea of a log_timezone seems to make sense.
I'll code that up and see if there are any unexpected gotchas.
regards, tom lane
On Aug 3, 2007, at 10:33 , Tom Lane wrote: > People who find the above arguments compelling would certainly be free > to set their log_timezone to GMT. Those who don't find them > compelling > should not be forced to deal in GMT. The fact that Postgres has > always > logged in system local time, and we've had no complaints about that, > suggests to me that most people prefer local-time logging. I've most likely missed something as I'm unfamiliar with this area, but would it make sense to record the time zone offset? Then whether its in local time or UTC, it's always marking a unique instant in time. Michael Glaesemann grzm seespotcode net
"Andrew Dunstan" <andrew@dunslane.net> writes: > That's probably the worst of all possible options. Two very common uses of > CSVlogs will be a) to load them into a PostgreSQL table and b) to load them into > a spreadsheet such as Excel. In both cases having a Unix epoch time rather than > a timestamp is likely to be very annoying. Strangely those are precisely the use cases I was thinking of as well. For both of those cases you need a timestamp which is unambiguous and understood everywhere. Any text representation is going to depend on other tools using the a compatible parser. Integers can be parsed by anything. But perhaps I overestimate Excel's abilities. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
> Andrew Dunstan <andrew@dunslane.net> writes: >> Tom's idea of a log_timezone seems to make sense. > > I'll code that up and see if there are any unexpected gotchas. BTW, windows user should do how.? How do you think? Does it say "Set up an environment variable"? set TZ= Regards, Hiroshi Saito
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> BTW, windows user should do how.? How do you think?
> Does it say "Set up an environment variable"?
> set TZ=
What do they do now to set the postmaster's "timezone"? That would
determine log_timezone too, if they don't override it in
postgresql.conf.
regards, tom lane
Tom Lane wrote: > "Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes: >> BTW, windows user should do how.? How do you think? >> Does it say "Set up an environment variable"? >> set TZ= > > What do they do now to set the postmaster's "timezone"? That would > determine log_timezone too, if they don't override it in > postgresql.conf. I'd expect >99% of Windows users set their timezone using the control panel. This gets mapped to the TZ in postgresql using the table win32_tzmap in src/timezone/pgtz.c. //Magnus
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom's idea of a log_timezone seems to make sense.
Here's a preliminary patch for this --- no docs yet, but code is all
there. Seems to work OK. The patch is larger than it'd really have to
be because I chose to rename global_timezone to session_timezone;
I thought it'd be confusing to have something called "global_timezone"
that actually has a narrower scope than "log_timezone".
This wouldn't do for backpatching of course. My inclination for a
backpatch is to just take %Z out of the strftime formats for Windows,
as we did in several places already.
Comments?
regards, tom lane
Attachment
Hi Tom-san. My it looks very great.!!! I was operating in the seen last night patch..... Naturally, your thing is more great. Thank you very much!:-) Regards, Hiroshi Saito From: "Tom Lane" <tgl@sss.pgh.pa.us> > Andrew Dunstan <andrew@dunslane.net> writes: >> Tom's idea of a log_timezone seems to make sense. > > Here's a preliminary patch for this --- no docs yet, but code is all > there. Seems to work OK. The patch is larger than it'd really have to > be because I chose to rename global_timezone to session_timezone; > I thought it'd be confusing to have something called "global_timezone" > that actually has a narrower scope than "log_timezone". > > This wouldn't do for backpatching of course. My inclination for a > backpatch is to just take %Z out of the strftime formats for Windows, > as we did in several places already. > > Comments? > > regards, tom lane > > -------------------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >