Thread: Copy from/to asymmetry
I set my time zone to GMT+8 today (because the "official" timezone prescribed
by the FreeBSD timezone database for my location is "CST", which was causing
all sorts of other problems elsewhere).
Tonight, when I did my nightly data transfer (consisting of "copy to" a
bunch of tables and concatenating them together into a "pg_dump" type of
script file, copying the file over, and then loading with psql), the
backend was very unhappy.
Every "copy from" block that contained a date crashed. This was particularly
unpleasant, because the script is bracketed within a single transaction block,
and each table is emptied ("delete from") before new data is copied in. As
a result of the crashes, the transaction aborted, but psql kept on processing
away, emptying tables, crashing, and repeat.
This was on a soon-to-be production e-commerce server.
I was able to recover in a few minutes by manually editing the script file
to replace all "GMT+8" with "+0800". Had this happened during an automated
transfer on a live system, however, the problem could have been severe.
I assume that my backups are similarly corrupted.
I looked through dt.c, and ParseDateTime appears to assume that timezones
are either strictly alphabetic or of the form "+0000". EncodeDateTime,
on the other hand, blindly spits out whatever the operating system gives it
from localtime().
It seems to me there are two separate problems: 1. x == datetime_in(datetime_out(x)) should always be true for all
validx. 2. psql should exit with an error status if it receives a fatal error from the backend and isatty(0) is
false.
-Michael Robinson
> I set my time zone to GMT+8 today (because the "official" timezone prescribed
> by the FreeBSD timezone database for my location is "CST", which was causing
> all sorts of other problems elsewhere).
...
> I was able to recover in a few minutes by manually editing the script file
> to replace all "GMT+8" with "+0800".
...
> I assume that my backups are similarly corrupted.
Sure, if you were running with a similarly unusual timezone format.
> I looked through dt.c, and ParseDateTime appears to assume that timezones
> are either strictly alphabetic or of the form "+0000".
Right, those are the forms we have seen or heard about (the minutes
field in the second form is optional). Yours is a new one for me.
> EncodeDateTime,
> on the other hand, blindly spits out whatever the operating system gives it
> from localtime().
Yup. afaik this is the only way to get daylight savings time info
since there is no api to do so otherwise. Since this is the very first
report of this style of timezone, I don't feel too guilty, and it will
be easy to fix (I hope anyway).
> 1. x == datetime_in(datetime_out(x)) should always be true for all valid x.
Impossible to do apriori, given that we rely on the system to provide
timezone info for output. However, we try to fix all unusual cases,
and afaik there are no reasonable formats we have rejected for
support. I'm leaving town for a couple of days, but will look at it
when I return.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes: >> EncodeDateTime, >> on the other hand, blindly spits out whatever the operating system gives it >> from localtime(). > >Yup. afaik this is the only way to get daylight savings time info >since there is no api to do so otherwise. Since this is the very first >report of this style of timezone, I don't feel too guilty, and it will >be easy to fix (I hope anyway). The GMT+8 format is part of the POSIX standard (at least according to the zoneinfo source file). In the meantime, I've created a new zoneinfo file with ISO "+0800" format, as a workaround. (To make matters worse, I discovered that POSIX GMT+8 == ISO -0800 ; in other words, the semantics of the sign character are reversed in the two standards.) >> 1. x == datetime_in(datetime_out(x)) should always be true for all valid x. >Impossible to do apriori, given that we rely on the system to provide >timezone info for output. However, we try to fix all unusual cases, >and afaik there are no reasonable formats we have rejected for >support. Perhaps, if the system supports strptime(), this function could be used as a last-ditch effort by ParseDateTime before returning an error. That would solve all cases where the datetime_in timezone equals the system timezone setting. Or, maybe just use strptime() outright. I don't know, it's just a suggestion. -Michael Robinson
On 2000-01-13, Michael Robinson mentioned: > 2. psql should exit with an error status if it receives a fatal error > from the backend and isatty(0) is false. This is already accomplished in the current sources. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> The GMT+8 format is part of the POSIX standard (at least according to
> the zoneinfo source file). In the meantime, I've created a new zoneinfo
> file with ISO "+0800" format, as a workaround. (To make matters worse, I
> discovered that POSIX GMT+8 == ISO -0800 ; in other words, the semantics of
> the sign character are reversed in the two standards.)
Yuck.
> Perhaps, if the system supports strptime(), this function could be used as
> a last-ditch effort by ParseDateTime before returning an error. That would
> solve all cases where the datetime_in timezone equals the system timezone
> setting.
How? strptime() needs a formatting string, so you would somehow need
to set it beforehand to *exactly* the correct value. And...
> Or, maybe just use strptime() outright. I don't know, it's just a suggestion.
The other problem with using system-supplied routines for this is that
they invariably fail for years outside the Unix system time range. So
we need to do enough parsing to figure out what the year might be, and
by that time we may as well finish it ourselves...
Anyway, I'll be looking at it sometime soon.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California