Thread: Postgres storing time in strange manner
I don't have a clue why it's doing this - has anyone else seen this sort of behavior, or know why it might be doing it? It shows times wrong, for instance, it shows 00:04:60 where it should show 00:05:00. See below: gabrielle=# select * from scheduled_query_groups; group_id | group_name | group_type_id | interval | run_at | last_run | exec_upon_completion ----------+------------+---------------+----------+-------------+------------ ------------+---------------------- 2 | test | 1 | 00:04:60 | 00:00:00-05 | 0001-01-01 04:59:60.00 | (1 row) gabrielle=# \d scheduled_query_groups; Table "scheduled_query_groups" Column | Type | Modifiers ----------------------+--------------------------+--------------------------- ------------------------------- group_id | integer | not null default nextval('sq_groups_group_id_seq'::text) group_name | character varying(32) | not null group_type_id | integer | not null interval | interval | not null default '5 minutes' run_at | time with time zone | not null default '00:00:00 EST' last_run | timestamp with time zone | not null default '0001-01-01 00:00:00 EST' exec_upon_completion | character varying(128) | Primary key: scheduled_query_groups_pkey Unique keys: scheduled_query__group_name_key Triggers: RI_ConstraintTrigger_16607 -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Lost Terminal.
On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > I don't have a clue why it's doing this - has anyone else seen this sort of > behavior, or know why it might be doing it? It shows times wrong, for > instance, it shows 00:04:60 where it should show 00:05:00. See below: There are actually 61 seconds in some minutes. In order to accommodate leap seconds, PostgreSQL allows this to happen -- similarly to how it will also allow 366 days in some years. -- Rod Taylor
On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: > On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > > I don't have a clue why it's doing this - has anyone else seen this sort > > of behavior, or know why it might be doing it? It shows times wrong, for > > instance, it shows 00:04:60 where it should show 00:05:00. See below: > > There are actually 61 seconds in some minutes. In order to accommodate > leap seconds, PostgreSQL allows this to happen -- similarly to how it > will also allow 366 days in some years. How then, am I supposed to explain to a web interface user that when they just entered 5:00:00, it's going to sometimes show up to 4:59:60? I'm entering an exact timestamp, that being 5:00:00. Regardless of how many seconds you claim were in the former minute, it should not subtract a second from my entry, because 5:00:00 by your definition would mean 4:59 and 61 seconds. -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Dance like nobody's watching.
> On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: >> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: >>> I don't have a clue why it's doing this - has anyone else seen this sort >>> of behavior, or know why it might be doing it? It shows times wrong, for >>> instance, it shows 00:04:60 where it should show 00:05:00. See below: >> >> There are actually 61 seconds in some minutes. In order to accommodate >> leap seconds, PostgreSQL allows this to happen -- similarly to how it >> will also allow 366 days in some years. True but irrelevant -- PG does not do accounting for leap seconds. The roundoff bug Casey is looking at is fixed in recent PG versions; I'd recommend an update to 7.2.2. regards, tom lane
> How then, am I supposed to explain to a web interface user that when they just > entered 5:00:00, it's going to sometimes show up to 4:59:60? Oh, I see. I thought you were doing some interval math on it. Yes, your right, it should be taken as being 5:00:00. > I'm entering an exact timestamp, that being 5:00:00. Regardless of how many > seconds you claim were in the former minute, it should not subtract a second > from my entry, because 5:00:00 by your definition would mean 4:59 and 61 > seconds. Either way, I've been unable to reproduce it with either 7.2 or 7.3 -- nor do I see any notes about that feature having been removed or carried over to current releases -- no regression tests for it in 7.3. Could you provide a complete test case, or confirm that it does what you expect in 7.3? 7.2.2: iqdb=# select '0001-01-01 4:59:60'::timestamptz; ERROR: Bad timestamp external representation '0001-01-01 4:59:60' -- Rod Taylor
On Sun, 2002-09-15 at 10:32, Tom Lane wrote: > > On Sunday 15 September 2002 10:11 am, Rod Taylor wrote: > >> On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote: > >>> I don't have a clue why it's doing this - has anyone else seen this sort > >>> of behavior, or know why it might be doing it? It shows times wrong, for > >>> instance, it shows 00:04:60 where it should show 00:05:00. See below: > >> > >> There are actually 61 seconds in some minutes. In order to accommodate > >> leap seconds, PostgreSQL allows this to happen -- similarly to how it > >> will also allow 366 days in some years. > > True but irrelevant -- PG does not do accounting for leap seconds. For some reason I thought it did accommodate it when I was still using 7.1. Ahh well, if the bug is fixed, then it's all good. -- Rod Taylor
On Sunday 15 September 2002 10:32 am, Tom Lane wrote: > The roundoff bug Casey is looking at is fixed in recent PG versions; > I'd recommend an update to 7.2.2. I'm running a freshly compiled version of 7.2.2 -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Dance like nobody's watching.
Casey Allen Shobe <cshobe@secureworks.net> writes: > On Sunday 15 September 2002 10:32 am, Tom Lane wrote: >> The roundoff bug Casey is looking at is fixed in recent PG versions; >> I'd recommend an update to 7.2.2. > I'm running a freshly compiled version of 7.2.2 Oh? On what platform? regards, tom lane
>> The kernel version is probably not relevant here; what's more >> interesting is the compiler version, compiler optimization level, >> and perhaps libc version. > cshobe@gabrielle:/www/htdocs$ gcc -v > Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/2.95.3/specs > gcc version 2.95.3 20010315 (release) > cshobe@gabrielle:/www/htdocs$ echo $CFLAGS > -O3 -march=i686 -mcpu=i686 -funroll-loops -ffast-math Bingo: it's the -ffast-math option that's causing the problem. See, eg, http://fts.postgresql.org/db/mw/msg.html?mid=1277557 If you rebuild without that, I think you'll find the problem goes away. ISTM that at one time we had considered actively discriminating against -ffast-math in CFLAGS in configure ... but I don't see any sign in current sources that we make any attempt to remove -ffast-math from environment-supplied CFLAGS. Peter, do you think that would be a reasonable thing to do? regards, tom lane
On Sunday 15 September 2002 02:14 pm, Tom Lane wrote: > Bingo: it's the -ffast-math option that's causing the problem. > See, eg, > http://fts.postgresql.org/db/mw/msg.html?mid=3D1277557 Thank you, I hadn't realized that they shouldn't be used together...I've us= ed=20 them for every package on my box :\...ah well, live and learn. --=20 Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Dance like nobody's watching.
Casey, > > There are actually 61 seconds in some minutes. In order to accommodate > > leap seconds, PostgreSQL allows this to happen -- similarly to how it > > will also allow 366 days in some years. > > How then, am I supposed to explain to a web interface user that when they just > entered 5:00:00, it's going to sometimes show up to 4:59:60? Leap minutes? Oh please. I'm gonna have to account for green martians next... DSL -- Con te partiro, su navi per mari Che io lo so, no, no non esistono piu Con te io li vivro. (Sartori F, Quarantotto E)
Tom Lane writes: > ISTM that at one time we had considered actively discriminating against > -ffast-math in CFLAGS in configure ... but I don't see any sign in > current sources that we make any attempt to remove -ffast-math from > environment-supplied CFLAGS. Peter, do you think that would be a > reasonable thing to do? Would it be possible to write a short test case that exhibits this behavior? -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes: > Tom Lane writes: >> ISTM that at one time we had considered actively discriminating against >> -ffast-math in CFLAGS in configure ... but I don't see any sign in >> current sources that we make any attempt to remove -ffast-math from >> environment-supplied CFLAGS. Peter, do you think that would be a >> reasonable thing to do? > Would it be possible to write a short test case that exhibits this > behavior? Not sure; apparently it depends on optimization level, so I'd be hesitant to assume that any short test case would reliably expose the problem. Also, aren't you trying to avoid run-time tests in configure? But if you prefer a run-time test, I'll see if I can cons one up. regards, tom lane
Tom Lane writes: > Not sure; apparently it depends on optimization level, so I'd be > hesitant to assume that any short test case would reliably expose > the problem. Also, aren't you trying to avoid run-time tests in > configure? If the test doesn't use any library function's run-time behavior, you can usually do something like main() { int a[(2.0+2.0==4.0)?1:-1] } This will fail to compile if the floating-point arithmetic is broken. Otherwise a good solution might be to print a warning if configure detects the flag. Or we can strip it out unconditionally, but that seems wrong. The user should be made aware of the problem. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes: > If the test doesn't use any library function's run-time behavior, you can > usually do something like > main() { > int a[(2.0+2.0==4.0)?1:-1] > } > This will fail to compile if the floating-point arithmetic is broken. However, unless gcc itself is compiled with -ffast-math, such an approach won't show up the bug. I had success with this test: #include <stdio.h> double d18000 = 18000.0; main() { int d = d18000 / 3600; printf("18000.0 / 3600 = %d\n", d); return 0; } Using Red Hat 7.2's compiler: [tgl@rh1 tgl]$ gcc -v Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98) I get: [tgl@rh1 tgl]$ gcc bug.c [tgl@rh1 tgl]$ ./a.out 18000.0 / 3600 = 5 -- right [tgl@rh1 tgl]$ gcc -ffast-math bug.c [tgl@rh1 tgl]$ ./a.out 18000.0 / 3600 = 4 -- wrong! You need the dummy global variable to keep the compiler from simplifying the division at compile time, else you get 5. With the test as exhibited, the -O level seems not to matter. regards, tom lane
I said: > I had success with this test: BTW, some digging in the assembly code shows that the actual problem is this: instead of emitting "x / 3600.0", with -ffast-math the compiler emits the equivalent of "x * (double) (1.0 / 3600.0)". It's the last-bit inaccuracy of the latter constant that's killing us. regards, tom lane
Ron Johnson <ron.l.johnson@cox.net> writes: > Out of curiosity: why does -ffast-math break the datetime rounding code? We dug into this last night, and it turns out that the culprit is code like int hour = time / 3600; where time is a double. This yields an exact result when done correctly, but with -ffast-math gcc will "improve" it to int hour = time * 0.000277777777777778; the constant being the nearest double value to 1.0 / 3600.0. The problem is that the constant is inexact and in fact is slightly too large; so for example if time is exactly 18000.0, you get a resulting hour value of 4, not 5, after truncation to integer. Repeated a couple more times, what should have been 5:00:00 comes out as 4:59:60 ... regards, tom lane
Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: > > Out of curiosity: why does -ffast-math break the datetime rounding code? > > We dug into this last night, and it turns out that the culprit is code > like > > int hour = time / 3600; > > where time is a double. This yields an exact result when done > correctly, but with -ffast-math gcc will "improve" it to > > int hour = time * 0.000277777777777778; > > the constant being the nearest double value to 1.0 / 3600.0. The > problem is that the constant is inexact and in fact is slightly too > large; so for example if time is exactly 18000.0, you get a resulting > hour value of 4, not 5, after truncation to integer. Repeated a couple > more times, what should have been 5:00:00 comes out as 4:59:60 ... Hard to imagine why anyone would want such an optimization. How much faster could it possibly be? I guess if you were doing only complex math approximations, it would be a win, but that isn't really proper for a database. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> with -ffast-math gcc will "improve" it to >> >> int hour = time * 0.000277777777777778; > Hard to imagine why anyone would want such an optimization. How much > faster could it possibly be? Back in ye bad olde days, there was probably an order-of-magnitude difference between the speed of a float multiply and that of a float divide; so this used to be a pretty standard sort of optimization. I can remember doing the equivalent thing by hand in source code. On modern hardware I doubt it makes much difference... regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> with -ffast-math gcc will "improve" it to > >> > >> int hour = time * 0.000277777777777778; > > > Hard to imagine why anyone would want such an optimization. How much > > faster could it possibly be? > > Back in ye bad olde days, there was probably an order-of-magnitude > difference between the speed of a float multiply and that of a float > divide; so this used to be a pretty standard sort of optimization. > I can remember doing the equivalent thing by hand in source code. > > On modern hardware I doubt it makes much difference... And you would have to do a heck of a lot of them to see a difference. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> > Out of curiosity: why does -ffast-math break the datetime rounding code? What code bits is this for? Is there a place where -fno-fast-math could be used as a CC option if the CC is gcc? After looking through gcc, using -O and -ffast-math will create broken code, but -O2 -ffast-math _should_ be okay. If it's not, then -O2 -fno-fast-math is likely the correct work around for GCC. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes: > Is there a place where -fno-fast-math > could be used as a CC option if the CC is gcc? configure is what I had in mind ;-). I can't think of any part of the code where we'd really want this sort of optimization enabled. > After looking through gcc, using -O and -ffast-math will create broken > code, but -O2 -ffast-math _should_ be okay. At least in the gcc shipped with Red Hat 7.2, it doesn't seem to matter: you get the wrong answer regardless of -O level. Here's the test case I used: [tgl@rh1 tgl]$ cat bug.c #include <stdio.h> double d18000 = 18000.0; main() { int d = d18000 / 3600; printf("18000.0 / 3600 = %d\n", d); return 0; } [tgl@rh1 tgl]$ gcc bug.c [tgl@rh1 tgl]$ ./a.out 18000.0 / 3600 = 5 -- right [tgl@rh1 tgl]$ gcc -O2 -ffast-math bug.c [tgl@rh1 tgl]$ ./a.out 18000.0 / 3600 = 4 -- wrong -- I get 4 if -ffast-math, -O doesn't affect it [tgl@rh1 tgl]$ gcc -v Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98) regards, tom lane
> > After looking through gcc, using -O and -ffast-math will create broken > > code, but -O2 -ffast-math _should_ be okay. > > At least in the gcc shipped with Red Hat 7.2, it doesn't seem to matter: > you get the wrong answer regardless of -O level. Here's the test case > I used: > > [tgl@rh1 tgl]$ cat bug.c > #include <stdio.h> > > double d18000 = 18000.0; > > main() { > int d = d18000 / 3600; > printf("18000.0 / 3600 = %d\n", d); > return 0; > } > [tgl@rh1 tgl]$ gcc bug.c > [tgl@rh1 tgl]$ ./a.out > 18000.0 / 3600 = 5 -- right > [tgl@rh1 tgl]$ gcc -O2 -ffast-math bug.c > [tgl@rh1 tgl]$ ./a.out > 18000.0 / 3600 = 4 -- wrong > -- I get 4 if -ffast-math, -O doesn't affect it > [tgl@rh1 tgl]$ gcc -v > Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98) Heh, chalk this one up as another Linux-ism then 'cause it's not present in FreeBSD -stable or -current. This actually makes me feel better about setting an option in the -devel port for turning on compilation with -O3. -sc stable$ gcc -v Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] current$ gcc -v Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.2.1 [FreeBSD] 20020901 (prerelease) -- Sean Chittenden
Tom Lane writes: > Sean Chittenden <sean@chittenden.org> writes: > > Is there a place where -fno-fast-math > > could be used as a CC option if the CC is gcc? > > configure is what I had in mind ;-). I can't think of any part of the > code where we'd really want this sort of optimization enabled. Today I read that __FAST_MATH__ is defined if -ffast-math is used, so it should be easy to write a test in configure. -- Peter Eisentraut peter_e@gmx.net