Обсуждение: [HACKERS] Problem with copying abstimes
Hello,
Sorry to bother you with this, but I think there is a small problem
with copying abstimes in v6.1.
I noticed an increment of 1 hour to abstime fields when
copying tables back into the database using the 'copy from'
command.
An example:
> postgres=> create table test(modtime abstime);
> CREATE
> postgres=> insert into test values('Fri Apr 04 18:41:48 1997');
> INSERT 18446
> postgres=> select * from test;
> modtime
> --------------------------------
> Fri Apr 04 18:41:48 1997 MET DST
> (1 row)
>
> postgres=> copy test to '/tmp/test';
> COPY
> postgres=> ^Z
> [1]+ Stopped psql
> % cat /tmp/test
> Fri Apr 04 18:41:48 1997 MET DST
Ok. Now watch this:
> % fg
> psql
>
> postgres=> delete from test;
> DELETE
> postgres=> copy test from '/tmp/test';
> COPY
> postgres=> select * from test;
> modtime
> --------------------------------
> Fri Apr 04 19:41:48 1997 MET DST
^
> (1 row)
The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this
has never happened before.
Platform is Linux 2.0.29. Compiler is gcc 2.7.2.1. C-library: 5.4.33
Any ideas?
Cheers,
Ronald
------------------------------
Ronald Baljeu wrote:
> ... I think there is a small problem with copying abstimes in v6.1.
> I noticed an increment of 1 hour to abstime fields when
> copying tables back into the database using the 'copy from'
> command.
<snip>
(table has one abstime field)
> > --------------------------------
> > Fri Apr 04 18:41:48 1997 MET DST
(copy out and back in using the copy command)
> > --------------------------------
> > Fri Apr 04 19:41:48 1997 MET DST
> ^
> The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this
> has never happened before.
> Platform is Linux 2.0.29. Compiler is gcc 2.7.2.1. C-library: 5.4.33
> Any ideas?
Yup. There is probably confusion in Postgres with your timezone
character strings. I have a similar installation environment, but my
US/Pacific timezone (PST8PDT) does not exhibit the behavior you see.
Please tell me your exact timezone environment and I can run some tests:
1) What is your default timezone (the character strings and the long
name)?
2) What does the link /etc/localtime point to?
3) Is there *really* a space between "MET" and "DST"? (sheesh)
In looking at the Postgres code, it appears that if there is a space
between "MET" and "DST" then that is the source of your trouble. "MET"
is Middle Europe (Standard) Time, and "DST" is ignored as a "noop" (this
behavior is inherited from previous versions of Postgres). If your
timezone is set to "MEST" or to "METDST" with no space you might get the
behavior you want.
- Tom
> > postgres=> create table test(modtime abstime);
> > postgres=> insert into test values('Fri Apr 04 18:41:48 1997');
> > postgres=> select * from test;
> > modtime
> > --------------------------------
> > Fri Apr 04 18:41:48 1997 MET DST
> > (1 row)
> >
> > postgres=> copy test to '/tmp/test';
> > COPY
> > postgres=> ^Z
> > [1]+ Stopped psql
> > % cat /tmp/test
> > Fri Apr 04 18:41:48 1997 MET DST
>
> Ok. Now watch this:
>
> > % fg
> > psql
> >
> > postgres=> delete from test;
> > DELETE
> > postgres=> copy test from '/tmp/test';
> > COPY
> > postgres=> select * from test;
> > modtime
> > --------------------------------
> > Fri Apr 04 19:41:48 1997 MET DST
> ^
> > (1 row)
>
> The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this
> has never happened before.
------------------------------
Thomas G. Lockhart wrote: > > Ronald Baljeu wrote: > > > > The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this > > has never happened before. > > Platform is Linux 2.0.29. Compiler is gcc 2.7.2.1. C-library: 5.4.33 > > Any ideas? > > Yup. There is probably confusion in Postgres with your timezone > character strings. I have a similar installation environment, but my > US/Pacific timezone (PST8PDT) does not exhibit the behavior you see. > > Please tell me your exact timezone environment and I can run some tests: > 1) What is your default timezone (the character strings and the long > name)? Err... Do you mean this? % date '+%Z' MET DST The TZ environment variable is empty. I don't know how to obtain the long name. > 2) What does the link /etc/localtime point to? /usr/lib/zoneinfo/Europe/Amsterdam > 3) Is there *really* a space between "MET" and "DST"? (sheesh) Yep. > In looking at the Postgres code, it appears that if there is a space > between "MET" and "DST" then that is the source of your trouble. "MET" > is Middle Europe (Standard) Time, and "DST" is ignored as a "noop" (this > behavior is inherited from previous versions of Postgres). If your > timezone is set to "MEST" or to "METDST" with no space you might get the > behavior you want. Thanks! I'll try that. I can't test this right away, because I had to downgrade because of this problem, in order to get our database up again as quickly as possible. Cheers, Ronald ------------------------------
On Thu, 19 Jun 1997, Ronald Baljeu wrote: > Thomas G. Lockhart wrote: > > > > Ronald Baljeu wrote: > > > > > > The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this > > > has never happened before. > > > Platform is Linux 2.0.29. Compiler is gcc 2.7.2.1. C-library: 5.4.33 > > > Any ideas? > > > > Yup. There is probably confusion in Postgres with your timezone > > character strings. I have a similar installation environment, but my > > US/Pacific timezone (PST8PDT) does not exhibit the behavior you see. > > On this topic, I had a similar problem a while back copying back in a dumped database. My timezone, as set by the standard zoneinfo file for my area, is EST. Even though PostgreSQL knowns this as Eastern USA, it is also typically the string used in Eastern Australia. Consequently, a copy out generates EST strings and then the copy in generates an unwanted 15 hour correction. Assuming that there is no official standard for timezone strings (see the thousands of netnews articles on this topic over the past decade), is there any way that a future release of Postgres could use a different mechanism for this feature. I'm sure that a lot of thought has already gone into this and so I may be totally wrong to suggest it but, would it be possible for the copy out/in code to be changed to convert to/from UTC (or GMT) time? (That is, dumped abstime values are always in UTC.) I think all machines understand the difference between UTC/GMT and local time. If this is not possible, would it be possible for Postgres to check the timezone string in the incoming abstime value against the local machine's timezone string and not make any adjustment if it matches? I think the first method would be more general and preferable. Could it be an option, keeping the current method for compatibility? Thanks. +----------------------+---+ | Ross Johnson | | E-Mail: rpj@ise.canberra.edu.au | Info Sciences and Eng|___| | University of Canberra | FAX: +61 6 2015227 | PO Box 1 | | Belconnen ACT 2616 | WWW: http://willow.canberra.edu.au/~rpj/ | AUSTRALIA | +--------------------------+ ------------------------------ End of hackers-digest V1 #392 *****************************
Ross Johnson wrote: > On this topic, I had a similar problem a while back copying back in a > dumped database. My timezone, as set by the standard zoneinfo file for my > area, is EST. Even though PostgreSQL knowns this as Eastern USA, it is > also typically the string used in Eastern Australia. Consequently, a copy > out generates EST strings and then the copy in generates an unwanted 15 > hour correction. Argh! I knew time is a difficult and messy problem, but this is *REALLY* bad. Does this mean we shouldn't rely on the timezone strings?! > Assuming that there is no official standard for timezone strings (see the > thousands of netnews articles on this topic over the past decade), is > there any way that a future release of Postgres could use a different > mechanism for this feature. > > I'm sure that a lot of thought has already gone into this and so I may be > totally wrong to suggest it but, would it be possible for the copy out/in > code to be changed to convert to/from UTC (or GMT) time? (That is, > dumped abstime values are always in UTC.) I think all machines understand > the difference between UTC/GMT and local time. I think this is a good idea. In that case we finally got rid of those #@$%*&!% timezones. Would this solve all abstime related problems, (including Daylight So-called-savings Time, time leaps and worm holes) ? I took a quick look at the source (src/backend/utils/adt/nabstime.c). It really boggles my mind. I think for the mean time (well, not quite: GMT+1 ;-) I'll just add a quick hack in my application in order to ignore 1 hour differences between 2 abstimes when comparing them. Cheers, Ronald ------------------------------
Ronald Baljeu wrote:
> > My timezone, as set by the standard zoneinfo file for my
> > area, is EST. Even though PostgreSQL knowns this as Eastern USA, it is
> > also typically the string used in Eastern Australia. Consequently, a copy
> > out generates EST strings and then the copy in generates an unwanted 15
> > hour correction.
>
> Argh! I knew time is a difficult and messy problem, but this is
> *REALLY* bad. Does this mean we shouldn't rely on the timezone strings?!
Only if you live in Australia (just joking...)
This seems to be a problem! Let's figure out a way to get the behavior
you want, even if it is as kludgy as having a USE_AUSTRALIAN_TIME
compile-time parameter.
> > Assuming that there is no official standard for timezone strings (see the
> > thousands of netnews articles on this topic over the past decade), is
> > there any way that a future release of Postgres could use a different
> > mechanism for this feature.
> >
> > I'm sure that a lot of thought has already gone into this and so I may be
> > totally wrong to suggest it but, would it be possible for the copy out/in
> > code to be changed to convert to/from UTC (or GMT) time? (That is,
> > dumped abstime values are always in UTC.) I think all machines understand
> > the difference between UTC/GMT and local time.
>
> I think this is a good idea. In that case we finally got rid of those
> #@$%*&!% timezones. Would this solve all abstime related problems,
> (including Daylight So-called-savings Time, time leaps and worm holes) ?
>
> I took a quick look at the source (src/backend/utils/adt/nabstime.c). It
> really boggles my mind.
Well, thank you! Though the really fun code is in dt.c...
> I think for the mean time (well, not quite: GMT+1 ;-) I'll just add
> a quick hack in my application in order to ignore 1 hour differences
> between 2 abstimes when comparing them.
I'll think about a conditional compilation for Australia timezones
(which would substitute the EST interpretation). Are there any other
3-character strings which . In the long run, perhaps we can support
entire new timezone rules as a run-time option, for example, but it may
be that for performance reasons a hardcoded table is best. In the
meantime, for dump/reload you could run the backend in GMT (I run all of
my machines in GMT) and this would eliminate the ambiguity in timezone.
btw, you probably already know that there _are_ several timezone strings
in Postgres to handle Australia; AEST, AESST, ACST, ACSST, AWST, AWSST,
CADT, CAST, EAST, LIGT, SADT, SAST, WADT, WAST, WDT, WST are all in
there. In fact, seems that Australia is overrepresented with time zone
support :)
- Tom
------------------------------
Woh, I am sorry to jump in here, but we have to go by whatever Unix gives us as a timezone. If Unix can't get it right, I don't think postgreSQL should be messing with it. Or am I missing something here. Are there timezones hard-coded into PostgreSQL? > > Ronald Baljeu wrote: > > > My timezone, as set by the standard zoneinfo file for my > > > area, is EST. Even though PostgreSQL knowns this as Eastern USA, it is > > > also typically the string used in Eastern Australia. Consequently, a copy > > > out generates EST strings and then the copy in generates an unwanted 15 > > > hour correction. > > > > Argh! I knew time is a difficult and messy problem, but this is > > *REALLY* bad. Does this mean we shouldn't rely on the timezone strings?! > > Only if you live in Australia (just joking...) > This seems to be a problem! Let's figure out a way to get the behavior > you want, even if it is as kludgy as having a USE_AUSTRALIAN_TIME > compile-time parameter. > > > > Assuming that there is no official standard for timezone strings (see the > > > thousands of netnews articles on this topic over the past decade), is > > > there any way that a future release of Postgres could use a different > > > mechanism for this feature. > > > > > > I'm sure that a lot of thought has already gone into this and so I may be > > > totally wrong to suggest it but, would it be possible for the copy out/in > > > code to be changed to convert to/from UTC (or GMT) time? (That is, > > > dumped abstime values are always in UTC.) I think all machines understand > > > the difference between UTC/GMT and local time. > > > > I think this is a good idea. In that case we finally got rid of those > > #@$%*&!% timezones. Would this solve all abstime related problems, > > (including Daylight So-called-savings Time, time leaps and worm holes) ? > > > > I took a quick look at the source (src/backend/utils/adt/nabstime.c). It > > really boggles my mind. > > Well, thank you! Though the really fun code is in dt.c... > > > I think for the mean time (well, not quite: GMT+1 ;-) I'll just add > > a quick hack in my application in order to ignore 1 hour differences > > between 2 abstimes when comparing them. > > I'll think about a conditional compilation for Australia timezones > (which would substitute the EST interpretation). Are there any other > 3-character strings which . In the long run, perhaps we can support > entire new timezone rules as a run-time option, for example, but it may > be that for performance reasons a hardcoded table is best. In the > meantime, for dump/reload you could run the backend in GMT (I run all of > my machines in GMT) and this would eliminate the ambiguity in timezone. > > btw, you probably already know that there _are_ several timezone strings > in Postgres to handle Australia; AEST, AESST, ACST, ACSST, AWST, AWSST, > CADT, CAST, EAST, LIGT, SADT, SAST, WADT, WAST, WDT, WST are all in > there. In fact, seems that Australia is overrepresented with time zone > support :) > > - Tom > > - -- Bruce Momjian maillist@candle.pha.pa.us ------------------------------
Bruce Momjian wrote:
> Woh, I am sorry to jump in here, but we have to go by whatever Unix
> gives us as a timezone. If Unix can't get it right, I don't think
> postgreSQL should be messing with it.
>
> Or am I missing something here. Are there timezones hard-coded into
> PostgreSQL?
Well, the good news is that Unix gets it as right as anyone, and the bad
news is that it isn't so good :/
As far as I know, there is no defined interface to get access to
non-default timezone information on Unix platforms (anyone having more
info is welcome to steer us to the right place). Postgres has a fairly
large table of timezone information (predating my involvement with it)
which is used to parse input time strings, and which can be used for
formatting output time strings for non-default time zones.
btw, when we finish solidifying v6.1, I have a few new time utility
functions ready to go; one of which is date_zone('timezone', datetime)
which will return a time translated to the specified time zone. This
takes advantage of the static timezone info already in Postgres.
- Tom
------------------------------
On Sat, 21 Jun 1997, Thomas G. Lockhart wrote: > Bruce Momjian wrote: > > Woh, I am sorry to jump in here, but we have to go by whatever Unix > > gives us as a timezone. If Unix can't get it right, I don't think > > postgreSQL should be messing with it. > > > > Or am I missing something here. Are there timezones hard-coded into > > PostgreSQL? > > Well, the good news is that Unix gets it as right as anyone, and the bad > news is that it isn't so good :/ > > As far as I know, there is no defined interface to get access to > non-default timezone information on Unix platforms (anyone having more > info is welcome to steer us to the right place). Postgres has a fairly > large table of timezone information (predating my involvement with it) > which is used to parse input time strings, and which can be used for > formatting output time strings for non-default time zones. The only true representation for timezones is as an offset from UTC, as used in email headers these days, at least those generated by sendmail. The problem is that people are more familiar with acronyms even though offsets are probably easier to use and to remember. I see at least three problems with acronyms:- 1) they aren't standard internationally, 2) they aren't very informative when seen outside of the zone, country or continent they apply to. (I could only guess at most of the zone strings used in my own country.) 3) different acronyms are used for the same zone offset as you travel north-south around the globe. As PostgreSQL is used all over the Internet, would it be too radical to suggest that PostgreSQL drop acronyms in favour of offsets as the default when displaying or parsing timezones, and especially when copying in and out? Perhaps a modifiable system table could provide correspondence between offsets and acronyms for those who want or need to use acronyms. +----------------------+---+ | Ross Johnson | | E-Mail: rpj@ise.canberra.edu.au | Info Sciences and Eng|___| | University of Canberra | FAX: +61 6 2015227 | PO Box 1 | | Belconnen ACT 2616 | WWW: http://willow.canberra.edu.au/~rpj/ | AUSTRALIA | +--------------------------+ ------------------------------
Ross Johnson wrote:
> The only true representation for timezones is as an offset from UTC, as
> used in email headers these days, at least those generated by sendmail.
> The problem is that people are more familiar with acronyms even though
> offsets are probably easier to use and to remember.
> I see at least three problems with acronyms:-
> 1) they aren't standard internationally,
> 2) they aren't very informative when seen outside of the zone, country or
> continent they apply to. (I could only guess at most of the zone strings
> used in my own country.)
Well, Australia _does_ seem to have a lot of them :)
> 3) different acronyms are used for the same zone offset as you travel
> north-south around the globe.
> As PostgreSQL is used all over the Internet, would it be too radical to
> suggest that PostgreSQL drop acronyms in favour of offsets as the default
> when displaying or parsing timezones, and especially when copying in and
> out?
Good point! Try "set DateStyle to 'ISO,'" and let me know how you like
it (include the trailing comma until the jumbo-patch comes out in a
couple of weeks). If you want to have a different default for your
installation, look in src/backend/utils/init/globals.c and change
USE_POSTGRES_DATES to USE_ISO_DATES. This output option works for
"datetime" but not for "abstime"; I may be allowed to include the new
output formatter for abstime in the jumbo patch or we may need to wait
for v6.2. btw, the input parser for both datetime and abstime already
understands ISO formats, so you can specify that style for input.
Perhaps we could reach a consensus (or take a vote) on the preferred
default time format for v6.2; Y2000 troubles may change a lot of
people's preferences for time formats and ISO-8601 (which the Postgres
ISO date format should be compatible with) is supposed to help...
- Tom
------------------------------
Thomas G. Lockhart wrote:
> Ronald Baljeu wrote:
> > I took a quick look at the source (src/backend/utils/adt/nabstime.c). It
> > really boggles my mind.
>
> Well, thank you!
You're welcome!
> Though the really fun code is in dt.c...
Aha... So here it is! I was looking at the wrong place (bummer :) )
I was wondering where you would put your lookup table for timezone strings.
Silly me. I should have done grep -i 'MET'...
^
> btw, you probably already know that there _are_ several timezone strings
> in Postgres to handle Australia; AEST, AESST, ACST, ACSST, AWST, AWSST,
> CADT, CAST, EAST, LIGT, SADT, SAST, WADT, WAST, WDT, WST are all in
> there. In fact, seems that Australia is overrepresented with time zone
> support :)
Well, if this planet would start turning at random directions
like a bowling ball, Australia shouldn't have to worry about having
too few timezones in directions other than East-West :-)
Cheers,
Ronald
------------------------------
Ross Johnson wrote: > As PostgreSQL is used all over the Internet, would it be too radical to > suggest that PostgreSQL drop acronyms in favour of offsets as the default > when displaying or parsing timezones, and especially when copying in and > out? Perhaps a modifiable system table could provide correspondence > between offsets and acronyms for those who want or need to use acronyms. I agree, but I think there is more to it. In /usr/lib/zoneinfo there are files describing *** for almost each country *** (and there are many): leap seconds, transition times and other time related info. Check out the man-page tzfile(5). So I guess the time difference between local time and UTC for a specific country may change many times a year (not only when we switch from/to Daylight Savings Time). But I'm positive Tom knows a lot more about this. Cheers, Ronald ------------------------------
With Shakespearian flourish, "Thomas G. Lockhart" writes:
> Ronald Baljeu wrote:
> >
> > I took a quick look at the source (src/backend/utils/adt/nabstime.c). It
> > really boggles my mind.
>
> Well, thank you! Though the really fun code is in dt.c...
I can vouch for that!
Thomas, I have your timezone-handling patches and will be testing
them later. It looks like they don't specifically address the AIX 3.2.5
problems, but do handle some niggling inconsistencies I'd been
experiencing after my own fixes and will make my patches smaller and
cleaner.
The main AIX 3.2.5 problem is that USE_POSIX_TIME cannot be applied, as
AIX 3.2.5 supports neither int timezone (even tho configure seems to
think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The
non-POSIX time handling seems just plain broken. My patches will therefore
only affect AIX 3.2.5 and NeXTstep, the other platform without POSIX_TIME.
Last I heard from Darren (as of Friday) he hadn't tested under AIX 3.2.5.
AIX 4.1 supports POSIX_TIME and is therefore not a problem.
...Are there any NeXTstep'ers out there? I'd like to get my patches
tested for use on that platform too, as I don't _expect_ 6.1 to work
on NeXTstep in its current state. (If it does, I'd be even more
interested in hearing about that!!)
-Frank
- --
Frank R. Dana, Jr. Senior Associate Programmer
danaf@ans.net ANS Communications
(914) 789-5449 100 Clearbrook Rd. Elmsford, NY 10523
Pager: 800-946-4646 pin# 1420717
------------------------------
> Frank Dana wrote: > > ... > The main AIX 3.2.5 problem is that USE_POSIX_TIME cannot be applied, as > AIX 3.2.5 supports neither int timezone (even tho configure seems to > think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The > non-POSIX time handling seems just plain broken. My patches will therefore > only affect AIX 3.2.5 and NeXTstep, the other platform without POSIX_TIME. > > Last I heard from Darren (as of Friday) he hadn't tested under AIX 3.2.5. > AIX 4.1 supports POSIX_TIME and is therefore not a problem. Good timing...running the regression on 325 right now... I compiled it by defining HAVE_INT_TIMEZONE in config.h and leaving USE_POSIX_TIME defined. Compiled very nicely. datetime test passed, but the abstime, tinterval and horology tests failed. No crashes or core dumps, but some "Bad external representation" errors in the log file. If you can live with datetime and not abstime, etc..., maybe give this a shot until they're fixed. I personally'll wait for the "Grand Unified Date", ala Oracle, but me thinks I'm gonna need a very comfy chair to wait in. :) :) Darren darrenk@insightdist.com ------------------------------
> > AIX 3.2.5 supports neither int timezone (even tho configure seems to
> > think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The
> > non-POSIX time handling seems just plain broken. My patches will therefore
> > only affect AIX 3.2.5 and NeXTstep, the other platform without POSIX_TIME.
> > AIX 4.1 supports POSIX_TIME and is therefore not a problem.
I would think that NextStep is at risk of getting dropped from Postgres
support, since it does not have shared memory or, apparently, anything
close to POSIX time support. If there is someone running on NextStep, it
would be good if they would make themselves known so we can see what the
NextStep OS support needs to be (can't believe it _still_ doesn't have
anything like POSIX-compatible time support).
Hey Darren or Frank, you haven't indicated how stale AIX 3.2.5 is. Why
should anyone keep spending time on it when AIX 4.1 is available?
> I compiled it by defining HAVE_INT_TIMEZONE in config.h and leaving
> USE_POSIX_TIME defined. Compiled very nicely. datetime test passed,
> but the abstime, tinterval and horology tests failed. No crashes or core
> dumps, but some "Bad external representation" errors in the log file.
Those might be normal; there are some tests for illegal time strings.
> If you can live with datetime and not abstime, etc..., maybe give this a shot
> until they're fixed.
I think the only fixes already in would help horology, but not abstime
and tinterval. Need more info on problems (but maybe not, if AIX4.1
doesn't show the same problems).
> I personally'll wait for the "Grand Unified Date", ala
> Oracle, but me thinks I'm gonna need a very comfy chair to wait in. :)
Uh, what does a "Grand Unified Date" do which datetime doesn't? (It's OK
to keep the list short :)
- Tom
------------------------------
End of hackers-digest V1 #400
*****************************
> > > > AIX 3.2.5 supports neither int timezone (even tho configure seems to > > > think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The > > > non-POSIX time handling seems just plain broken. My patches will therefore > > > only affect AIX 3.2.5 and NeXTstep, the other platform without POSIX_TIME. > > > AIX 4.1 supports POSIX_TIME and is therefore not a problem. > > I would think that NextStep is at risk of getting dropped from Postgres > support, since it does not have shared memory or, apparently, anything > close to POSIX time support. If there is someone running on NextStep, it > would be good if they would make themselves known so we can see what the > NextStep OS support needs to be (can't believe it _still_ doesn't have > anything like POSIX-compatible time support). > > Hey Darren or Frank, you haven't indicated how stale AIX 3.2.5 is. Why > should anyone keep spending time on it when AIX 4.1 is available? (speaking for Frank) b/c there are companies like ours which have a LARGE deployment of AIX-3.2.5 boxes (in our case model 220's) and have absolutely no intention of "upgrading" them to run AIX-4.x. Especially since we would gain absolutely nothing.... except for a slower running box. (plus we'd have to port our software to "yet another platform", since we have alot of custom written code that runs on these boxes.) Our "upgrade" of an AIX-3.2.5 box would be to throw it out in favor of something running BSDi or Solaris. But that will take a long time to do. Anyway, assuming we favorably work out the problems we're having with postgres-6.1, we'll end up with a very large deployment (like over 1000 machines, a mixture of AIX-3.2.5, BSDi-2.1, and Solaris-2.5.1). We're still doing the testing/modeling/justification right now. (assuming we dont work it out would force us back to the drawing board in search of another database that supports all of our platforms). Roger. - -- Roger Florkowski - ANS CO+RE Systems, Inc. - roger@ans.net ------------------------------
With Shakespearian flourish, Roger Florkowski writes > > Hey Darren or Frank, you haven't indicated how stale AIX 3.2.5 is. Why > > should anyone keep spending time on it when AIX 4.1 is available? > > (speaking for Frank) [...] Now there's a twist on std.disclaimer! "I don't speak for my company, but they speak for me!" <grin> Here's what I was going to send on the matter -- I was hoping to put this at the bottom of na "I finally got it working!" message, but I haven't yet. 8-P With Shakespearian flourish, "Thomas G. Lockhart" writes: > btw, how stale is 3.2.5? Would it be appropriate to declare pre-v4.1 AIX > as "unsupported"? IMHO that's inappropriate, as even IBM hasn't done that yet. (They plan to in December, last I heard.) It's certainly more supported (tho less widely used) than SunOS. This problem isn't just limited to AIX anyway -- any system that doesn't have an int timezone would be affected, as the bug is in the code that handles timezone information when the timezone int (or the tm_gmtoff element in struct tm) is not available. Of course, NeXTstep seems to be the only other one that fits that category, and it's probably going away. While it would be convenient to drop AIX 3.2.5 and make this datetime bug disappear, I'll have it cleaned up shortly and then of any new (old) systems come along that need it, the code will be there. The rest of the AIX 3.2.5 problems Darren seems to have corrected. - -- Frank R. Dana, Jr. Senior Associate Programmer danaf@ans.net ANS Communications (914) 789-5449 100 Clearbrook Rd. Elmsford, NY 10523 Pager: 800-946-4646 pin# 1420717 ------------------------------
- --NeXT-Mail-1349238861-1
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
You wrote:
> > > AIX 3.2.5 supports neither int timezone (even tho configure seems =
to
> > > think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The
> > > non-POSIX time handling seems just plain broken. My patches will
> > > therefore only affect AIX 3.2.5 and NeXTstep, the other platform
> > > without POSIX_TIME. AIX 4.1 supports POSIX_TIME and is therefore =
not
> > > a problem.
>=20
> I would think that NextStep is at risk of getting dropped from =
Postgres
> support, since it does not have shared memory or, apparently, anything
> close to POSIX time support. If there is someone running on NextStep, =
it
> would be good if they would make themselves known so we can see what =
the
> NextStep OS support needs to be (can't believe it _still_ doesn't have
> anything like POSIX-compatible time support).
Beep. Wait a moment...
hactar:6> uname -a
NEXTSTEP hactar Rose2L 3.3 (7100LC) HPPA
Accepted ?
NEXTSTEP indeed has no SysV IPC and shared memory interface, but there's =
a commercial third-party product, SysVIPC (free for academic use), that =
implements those routines based on the Mach shared memory interface. =
That's enough to compile and run Postgres95 (in fact the lib was written =
for this purpose). Maybe one should add this to the docs (should I write =
down a paragraph ?).
NEXTSTEP comes with a POSIX compatibility library, but this is heavily =
broken so that everybody recommends against using it, therefore one is =
stuck with plain the BSD 4.3 interfaces.
Regarding USE_POSIX_TIME: =46rom browsing through dt.c, datetime.c and =
nabstime.c, it looks like NEXTSTEP's ctime(3) has all you need for =
USE_POSIX_TIME. Since the NEXTSTEP port is not functional at all at the =
moment, I would suggest to switch to USE_POSIX_TIME for NEXTSTEP and =
drop the old stuff. If that should break time support for NEXTSTEP, we =
have to look at it later again.
Ok ?
Gregor
- ---
| Gregor Hoffleit Mathematisches Institut, Uni HD =
|
| flight@mathi.uni-heidelberg.de INF 288, 69120 Heidelberg, Germany =
|
| (NeXTmail, MIME) (49)6221 54-5771 fax 54-8312 =
|
| PGP Key fingerprint =3D 23 8F B3 38 A3 39 A6 01 5B 99 91 D6 F2 AC CD =
C7 |
- --NeXT-Mail-1349238861-1
Content-Type: text/enriched; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
You wrote:
> > > AIX 3.2.5 supports neither int timezone (even tho configure seems =
to
> > > think it does) nor the Sun/DEC tm struct with tm_gmtoff field. The
> > > non-POSIX time handling seems just plain broken. My patches will
> > > therefore only affect AIX 3.2.5 and NeXTstep, the other platform
> > > without POSIX_TIME. AIX 4.1 supports POSIX_TIME and is therefore =
not
> > > a problem.
>=20
> I would think that NextStep is at risk of getting dropped from =
Postgres
> support, since it does not have shared memory or, apparently, anything
> close to POSIX time support. If there is someone running on NextStep, =
it
> would be good if they would make themselves known so we can see what =
the
> NextStep OS support needs to be (can't believe it _still_ doesn't have
> anything like POSIX-compatible time support).
Beep. Wait a moment...
hactar:6> uname -a
NEXTSTEP hactar Rose2L 3.3 (7100LC) HPPA
Accepted ?
NEXTSTEP indeed has no SysV IPC and shared memory interface, but there's =
a commercial third-party product, SysVIPC (free for academic use), that =
implements those routines based on the Mach shared memory interface. =
That's enough to compile and run Postgres95 (in fact the lib was written =
for this purpose). Maybe one should add this to the docs (should I write =
down a paragraph ?).
NEXTSTEP comes with a POSIX compatibility library, but this is heavily =
broken so that everybody recommends against using it, therefore one is =
stuck with plain the BSD 4.3 interfaces.
Regarding USE_POSIX_TIME: =46rom browsing through dt.c, datetime.c and =
nabstime.c, it looks like NEXTSTEP's ctime(3) has all you need for =
USE_POSIX_TIME. Since the NEXTSTEP port is not functional at all at the =
moment, I would suggest to switch to USE_POSIX_TIME for NEXTSTEP and =
drop the old stuff. If that should break time support for NEXTSTEP, we =
have to look at it later again.
Ok ?
Gregor
- ---
|
<smaller> Gregor Hoffleit Mathematisches =
Institut, Uni HD |
| flight@mathi.uni-heidelberg.de INF 288, 69120 Heidelberg, Germany =
|
| (NeXTmail, MIME) (49)6221 54-5771 fax 54-8312 =
|
| PGP Key fingerprint =3D 23 8F B3 38 A3 39 A6 01 5B 99 91 D6 F2 AC CD =
C7 |
</smaller>
- --NeXT-Mail-1349238861-1--
------------------------------
> Hey Darren or Frank, you haven't indicated how stale AIX 3.2.5 is. Why > should anyone keep spending time on it when AIX 4.1 is available? I sent a message back to the list, but evidently it didn't make past our provider. Oh, well. 325 is very alive and kicking. IBM does currently support it, for how much longer is anyone's guess, but it _does_ have a _big_big_ install base. Answer to the 2nd part is $$$$$. Brings to mind the old adage that I can't begin to count how many times I'd wished I'd followed - "Don't !*@& with a working system." > > I personally'll wait for the "Grand Unified Date", ala > > Oracle, but me thinks I'm gonna need a very comfy chair to wait in. :) > > Uh, what does a "Grand Unified Date" do which datetime doesn't? (It's OK > to keep the list short :) I guess this boils down to my use of Oracle and my decided favoritism towards it and it's datatypes/functions. Postgres dates seem to be "too flexible" if you will. Postgres needs something like the TO_CHAR(), TO_DATE() and TO_NUMBER() functions for converting and formating the various data types. Might as well stoke the fire...*asbestos suit on* Why do we now have date, abstime and datetime? Why not deprecate the old postgres date and abstime for v7.0 and rename the datetime to be the ansi type date type? I realize that people will have to change scripts and code, but it happens, projects progress. Try to keep the old stuff around and you end of with messes like the seg/offset PC memory hack. :) Datetime seems to be able to do everything the other two can, why not [lt]ighten up the code a bit? Anyways, "Flexibility breeds bugs." Preparing for the heat... darrenk@insightdist.com ------------------------------
> > btw, how stale is 3.2.5? Would it be appropriate to declare pre-v4.1 AIX
> > as "unsupported"?
> IMHO that's inappropriate, as even IBM hasn't done that yet. (They plan
> to in December, last I heard.) It's certainly more supported (tho less
> widely used) than SunOS.
> This problem isn't just limited to AIX anyway -- any system that doesn't
> have an int timezone would be affected, as the bug is in the code that
> handles timezone information when the timezone int (or the tm_gmtoff
> element in struct tm) is not available. Of course, NeXTstep seems to
> be the only other one that fits that category, and it's probably going
> away. While it would be convenient to drop AIX 3.2.5 and make this
> datetime bug disappear, I'll have it cleaned up shortly and then of
> any new (old) systems come along that need it, the code will be there.
I'm not aware of other USE_POSIX_TIME/!HAVE_INT_TIMEZONE machines seeing
this problem. Is AIX4.1 in that same features category?
I thought that AIX3.2.5 has _some_ timezone support; if not then you
really are the only machine I've found other than NextStep which is in
that category.
- Tom
------------------------------
With Shakespearian flourish, "Thomas G. Lockhart" writes:
> [timezone problems with AIX 3.2.5]
> I'm not aware of other USE_POSIX_TIME/!HAVE_INT_TIMEZONE machines seeing
> this problem. Is AIX4.1 in that same features category?
No, it's not, as it supports a valid INT_TIMEZONE. Actually, I've been
removing USE_POSIX_TIME under AIX 3.2.5, since USE_POSIX_TIME/!INT_TZ
doesn't work for AIX either -- that falls back to using tm_gmtoff and
tm_isdst element to struct tm that are not available under AIX. So
the code I've been hacking at is the !USE_POSIX_TIME code (or, as
I've made it, the use-timezone-info-but-avoid-int-timezone code).
Ohhhhh crap. I just discovered the problem. I can't believe I didn't
hit this in my tests before.
AIX 3.2.5 does have a happily valid int timezone. Works fine. Until
you link with the BSD library. At which point it comes out hugely
negative (and time-travels your dates 25 years west of GMT.)
I haven't tested this with PostgreSQL itself yet, but I just compiled my
check-code without -lbsd and everything seems to have worked 100% fine.
If that works for the database, all we should need to do is rip out -lbsd.
> I thought that AIX3.2.5 has _some_ timezone support; if not then you
> really are the only machine I've found other than NextStep which is in
> that category.
It seems to have _SWITCHABLE_ POSIX/BSD timezone support (instead of just
letting them live happily together). Very strange. But I even found this
to be true in a little 20-line test program I put together. Which I've
just upgraded to AIX 4.1, and seems to show the same problem. I guess
maybe we use -lbsd for 3.2.5 and don't use it for 4.1? Hm. Otherwise
this should have showed up under AIX 4.1, too -- at least, it did when
I tested on the one AIX 4.1 system I have access to.
Regardless, compiling without -lbsd now. I'll let you know.
<fingers crossed>
-Frank
- --
Frank R. Dana, Jr. Senior Associate Programmer
danaf@ans.net ANS Communications
(914) 789-5449 100 Clearbrook Rd. Elmsford, NY 10523
Pager: 800-946-4646 pin# 1420717
------------------------------
> AIX 3.2.5 does have a happily valid int timezone. Works fine. Until > you link with the BSD library. At which point it comes out hugely > negative (and time-travels your dates 25 years west of GMT.) > > I haven't tested this with PostgreSQL itself yet, but I just compiled my > check-code without -lbsd and everything seems to have worked 100% fine. > If that works for the database, all we should need to do is rip out -lbsd. I used to take it out, but when it was moved after the -lm to find the right pow(), I stopped. What about fork vs vfork? If I took the -lbsd off, I had to fudge the config.h and undefine HAVE_VFORK. That was the only function from libbsd that was used. > It seems to have _SWITCHABLE_ POSIX/BSD timezone support (instead of just > letting them live happily together). Very strange. But I even found this > to be true in a little 20-line test program I put together. Which I've > just upgraded to AIX 4.1, and seems to show the same problem. I guess > maybe we use -lbsd for 3.2.5 and don't use it for 4.1? Hm. Otherwise > this should have showed up under AIX 4.1, too -- at least, it did when > I tested on the one AIX 4.1 system I have access to. So using libbsd broke 41 for you? I'll have to double check my stuff again. At one time I was compiling without libbsd after undefining HAVE_VFORK, so maybe I got my correct results then and haven't rerun the regression tests since the -lm and -lbsd were switched. *sigh*...more crap to track down...now I know why AIX rhymes with "aches"... darrenk ------------------------------
With Shakespearian flourish, Darren King writes:
> > I haven't tested this with PostgreSQL itself yet, but I just compiled my
> > check-code without -lbsd and everything seems to have worked 100% fine.
> > If that works for the database, all we should need to do is rip out -lbsd.
>
> I used to take it out, but when it was moved after the -lm to find the right
> pow(), I stopped. What about fork vs vfork? If I took the -lbsd off, I had
> to fudge the config.h and undefine HAVE_VFORK. That was the only function
> from libbsd that was used.
<nod> I ripped out libbsd and HAVE_VFORK (the hard way, via configure,
to make sure it wasn't hiding anything on me), and everything compiled
cleanly. datetime failed with only 2 errors, which seem to be the known
datetime/ timespan bug Thomas patched. abstime and horology also failed,
still checking those.
> So using libbsd broke 41 for you? I'll have to double check my stuff again.
> At one time I was compiling without libbsd after undefining HAVE_VFORK, so
> maybe I got my correct results then and haven't rerun the regression tests
> since the -lm and -lbsd were switched.
Using -lbsd under 4.1 broke the test script I sent you earlier. (-lm was
not involved.) Removing libbsd and the ftime() calls made int timezone
work fine. I can't test PostgreSQL under AIX 4.1, the 4.1 box I'm using
is a college alumni account with limited space.
However, the "new" ordering (-lm -lbsd) definitely didn't fix things
for me under AIX 3.2.5. Removing -lbsd/vfork seems to have helped.
> *sigh*...more crap to track down...now I know why AIX rhymes with "aches"...
Took you this long to come to that conclusion? 8)
-Frank
- --
Frank R. Dana, Jr. Senior Associate Programmer
danaf@ans.net ANS Communications
(914) 789-5449 100 Clearbrook Rd. Elmsford, NY 10523
Pager: 800-946-4646 pin# 1420717
------------------------------
> Postgres needs something like the TO_CHAR(), TO_DATE() and
> TO_NUMBER() functions for converting and formating the various data types.
Has 'em, or will (see below).
> Why do we now have date, abstime and datetime? Why not deprecate the old
> postgres date and abstime for v7.0 and rename the datetime to be the ansi type
> date type? I realize that people will have to change scripts and code, but it
> happens, projects progress. Try to keep the old stuff around and you end of
> with messes like the seg/offset PC memory hack. :)
>
> Datetime seems to be able to do everything the other two can, why not [lt]ighten
> up the code a bit? Anyways, "Flexibility breeds bugs."
Naw, no flames, although I might like to postpone this discussion for a
while since v7.0 is not coming for a while. Anyway, I have an Ingres
background, and so implemented functions date_trunc() and date_part()
which presumably do things like to_number(). (Have other neat functions
like date_zone() which gives you any timezone you specify, ready to go
in the next release). Most commercial RDBMSes are pretty old now, and
probably wouldn't do things the same way if they could do it over. So,
neither Oracle nor Ingres are probably the best models for the right way
to do it, and I suppose Postgres is not trying to become an Oracle
look-alike but rather a modern, lean, try-to-do-it-the-sensible-way
system.
from glancing at the SQL92 docs posted at Berkeley, ANSI SQL92 seems to
specify 4 date/time types (with one variant):
1) date
2) time
3) timestamp and "timestamp with timezone"
4) interval
Of course, SQL92 was carefully designed to not break existing databases
too much, so might have some stuff which is a waste of time. I would
propose that for v7.0 we migrate to the ANSI SQL date/time terminology,
and have the existing datetime type migrate to become timestamp, and the
existing timespan type become interval. date and time would stay defined
and become more ANSI-like if not so already.
In keeping with Postgres' international outlook, I would propose that
timestamp always have a timezone associated with it, and that date and
time be simple and timezone-independent.
abstime has been used since the beginning of (Unix) time in the
internals of Postgres, but I would suggest that it might be deprecated
as a user-level type.
Other comments?
- Tom
------------------------------
End of hackers-digest V1 #401
*****************************
> > Postgres needs something like the TO_CHAR(), TO_DATE() and
> > TO_NUMBER() functions for converting and formating the various data types.
>
> Has 'em, or will (see below).
Not quite...TO_CHAR() takes a date and a format string as args and then returns
a char-type of that date formatted according to the given format string.
Ex...
create table foo (bday date);
insert into foo values (TO_DATE('12-FEB-1969','DD-MON-YYYY'));
select TO_CHAR(bday, '"Birthday is" fmMonth ddth, Year') as Birthday from foo;
BIRTHDAY
- ------------------------------------------------------------------------------
Birthday is February 12th, Nineteen Sixty-Nine
I could send you the Oracle docs for the format if you'd like. They're very
powerful in generating reports or formatting for piping to scripts, etc.
> So,
> neither Oracle nor Ingres are probably the best models for the right way
> to do it, and I suppose Postgres is not trying to become an Oracle
> look-alike but rather a modern, lean, try-to-do-it-the-sensible-way
> system.
Not that I want postgres to be exactly like Oracle, but their date handling
is one of their strong points, IMHO.
> Of course, SQL92 was carefully designed to not break existing databases
> too much, so might have some stuff which is a waste of time. I would
> propose that for v7.0 we migrate to the ANSI SQL date/time terminology,
> and have the existing datetime type migrate to become timestamp, and the
> existing timespan type become interval. date and time would stay defined
> and become more ANSI-like if not so already.
As long as there's one datatype that has both date and time, and there's a
function to format the output of that type, I could live with it.
> abstime has been used since the beginning of (Unix) time in the
> internals of Postgres, but I would suggest that it might be deprecated
> as a user-level type.
Do I hear a vote of doing the same with oid? I'd better quit while I'm at
least close to even here. :) :)
> Other comments?
Coming from a math theory background, why are people so intent on separating
date and time? They're both _time_ really... :) One datatype that contains
both is the only "perfect" date or time type. (See what happens when I have
too much coffee in the morning? :)
Has anybody ever run the postgres code thru lint? I thought about it, but
the output could be scrolling for _years_ I'm afraid...
Darren darrenk@insightdist.com
------------------------------
> Has anybody ever run the postgres code thru lint? I thought about it, but > the output could be scrolling for _years_ I'm afraid... My feeling is that compiling gcc with -Wall is almost the same as lint, and we do that regularly. - -- Bruce Momjian maillist@candle.pha.pa.us ------------------------------
> Not quite...TO_CHAR() takes a date and a format string as args and then returns
> a char-type of that date formatted according to the given format string.
> create table foo (bday date);
> insert into foo values (TO_DATE('12-FEB-1969','DD-MON-YYYY'));
> select TO_CHAR(bday, '"Birthday is" fmMonth ddth, Year') as Birthday from foo;
>
> BIRTHDAY
> ------------------------------------------------------------------------------
> Birthday is February 12th, Nineteen Sixty-Nine
May be possible for v6.2. Lousy function names, though, wrt other
Postgres name conventions... So, TO_DATE() and TO_CHAR() are symmetric,
so that you can do the following (?):
select TO_CHAR(
TO_DATE('12-FEB-1969','"Birthday is" fmMonth ddth, Year'),
'"Birthday is" fmMonth ddth, Year');
> Not that I want postgres to be exactly like Oracle, but their date handling
> is one of their strong points, IMHO.
Boy, add some features and people get _so_ demanding :)
The first step was adding date/time types which are robust and general
enough to build on (datetime and timespan are, I believe). The next step
is to add functionality, so keep those suggestions coming.
> Coming from a math theory background, why are people so intent on separating
> date and time? They're both _time_ really... :) One datatype that contains
> both is the only "perfect" date or time type. (See what happens when I have
> too much coffee in the morning? :)
*slurp* Yeah, me too. I don't use date or time separately myself, and
don't see why others do. I suspect that historically date and time were
implemented before software was in anything other than a local
environment. Now with global networking and distributed computing, the
premise might be a bit dated, so to speak.
- Tom
------------------------------
> > Not quite...TO_CHAR() takes a date and a format string as args and then returns
> > a char-type of that date formatted according to the given format string.
> > create table foo (bday date);
> > insert into foo values (TO_DATE('12-FEB-1969','DD-MON-YYYY'));
> > select TO_CHAR(bday, '"Birthday is" fmMonth ddth, Year') as Birthday from foo;
> >
> > BIRTHDAY
> > ------------------------------------------------------------------------------
> > Birthday is February 12th, Nineteen Sixty-Nine
>
> May be possible for v6.2. Lousy function names, though, wrt other
> Postgres name conventions... So, TO_DATE() and TO_CHAR() are symmetric,
> so that you can do the following (?):
>
> select TO_CHAR(
> TO_DATE('12-FEB-1969','"Birthday is" fmMonth ddth, Year'),
> '"Birthday is" fmMonth ddth, Year');
Almost...
select TO_CHAR( +----------+ <<--
TO_DATE('12-FEB-1969','DD-MON-YYYY'),
'"Birthday is" fmMonth ddth, Year') from DUAL;
The format string to the TO_DATE tells it how to parse the string and then the
format string to TO_CHAR tells it how to print out the now date-type arg.
Notice the "from DUAL"...Oracle requires that you have a FROM clause, so they
have a table called DUAL with one field, DUMMY as a varchar2(1). Not really
important what is in DUMMY, just that the DUAL table exists, but on the copy
of personal oracle I have, DUMMY is a 'X'. Their way of letting you select
literals.
Is it in sql-92 if a "FROM" clause is required?
> > Not that I want postgres to be exactly like Oracle, but their date handling
> > is one of their strong points, IMHO.
>
> Boy, add some features and people get _so_ demanding :)
> The first step was adding date/time types which are robust and general
> enough to build on (datetime and timespan are, I believe). The next step
> is to add functionality, so keep those suggestions coming.
I agree about the first step and do appreciate the work/development you've done so far.
One of the great things about postgres is that the developers are free to put in
what they want, how they want and can try to copy/improve on the "best" things from
the commercial dbs or their own ideas. Sort of like a mongrel winning a dog show.
Might not be "pretty", but it has the best features of all the other dogs there. :)
Not that I'm calling postgres an "ugly mongrel" either...to the contrary...
Darren darrenk@insightdist.com
------------------------------
Darren King wrote:
>
>
> > abstime has been used since the beginning of (Unix) time in the
> > internals of Postgres, but I would suggest that it might be deprecated
> > as a user-level type.
>
> Do I hear a vote of doing the same with oid? I'd better quit while I'm at
^^^^^^^^^^^^^^^^^^^^^^^
> least close to even here. :) :)
And you hear my vote too!
>
> > Other comments?
>
> Coming from a math theory background, why are people so intent on separating
> date and time? They're both _time_ really... :) One datatype that contains
Just because I'm not interested about time in some cases.
And I don't like to use 'datetime >= _a_day_ and datetime < _next_day_'
to get records for a day using indices, because I can't use indices
for "TO_CHAR(datetime, 'DD-MM-YYYY') = _a_day_" (we can't use
functions in multi-column indices - currently, at least).
> both is the only "perfect" date or time type. (See what happens when I have
> too much coffee in the morning? :)
Vadim
------------------------------