Correction of how to use TimeZone by ControlFile(xlog.c)

"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