Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
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 [EMAIL PROTECTED] Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
On Wed, Aug 01, 2007 at 11:17:21AM +0900, Hiroshi Saito wrote: Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Magnus Hagander [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hi. From: Tom Lane [EMAIL PROTECTED] Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Magnus Hagander wrote: On Wed, Aug 01, 2007 at 11:17:21AM +0900, Hiroshi Saito wrote: Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Tom Lane [EMAIL PROTECTED] writes: Magnus Hagander [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Tom Lane wrote: Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Andrew Dunstan [EMAIL PROTECTED] 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 binQH88Ti2QXM.bin Description: log_timezone_1.patch.gz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
Hi Tom-san. From: Tom Lane Hiroshi Saito [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
