Обсуждение: Re(2): 7.0.3 BUG
tgl@sss.pgh.pa.us writes: >"pgsql-sql" <pgsql-sql@fc.emc.com.ph> writes: >> migrate=# select userid from users where userid = 'reynan@yahoo.com'; >> userid >> --------------------- >> reynan@yahoo.com >> admin >> (2 rows) > >That's a tad, um, startling :-( > >However, you haven't given us nearly enough information to have a shot >at figuring out what's going on. > > regards, tom lane The query used indexscan. I tried using trim in userid and I got something like ... migrate=# select userid from users where trim(userid) = 'reynan@yahoo.com'; userid--------------------- reynan@yahoo.com(1row) I thought it was a varchar problem ('coz userid is varchar) of 7.0.3 so I changed to 7.0.2. Is it really a varchar bug? Anyway, I dumped all the database. When I was restoring it in 7.0.2 I got these ... You are now connected as new user root. CREATE You are now connected as new user pgsql. ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 302, Bad timestamp external representation '2000-02-29 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 13, Bad timestamp external representation '1970-01-01 08:04:60.00+08' PQendcopy: resetting connection ERROR: copy: line 24, Bad timestamp external representation '1970-01-01 08:04:60.00+08' PQendcopy: resetting connection You are now connected as new user root. ERROR: copy: line 2, Bad timestamp external representation '1999-12-24 00:00:60.00+08' PQendcopy: resetting connection You are now connected as new user pgsql. CREATE I took a look at the database named 'migrate' (this is where the error occured), the tables were all empty. Most of the tables have field like this... createdate | timestamp | not null default now() Weird because those timestamps were generated by default now(). Is this an another bug (timestamp bug)? Take a look at this ... migrate=# select version(); version ---------------------------------------------------------------PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 (1 row) migrate=# select '2000-01-05 00:00:60.00+08'::timestamp; ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08' migrate=# select '2000-11-25 14:05:23.00+08'::timestamp; ?column? ---------------------------2000-11-25 14:05:23.00+08 (1 row) migrate=# create table testing (datetime timestamp); CREATE migrate=# insert into testing values('2000-01-05 00:00:60.00+08'); ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08' migrate=# insert into testing values('2000-11-25 14:05:23.00+08'); INSERT 6834235 1 migrate=# Thanks, sherwin
"pgsql-sql" <pgsql-sql@fc.emc.com.ph> writes: > ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05 > 00:00:60.00+08' > Weird because those timestamps were generated by default now(). Weird, all right. I can get 7.0.2 to emit an incorrect representation like that by constructing a fractional-second value that needs to be rounded off: play=> set TimeZone TO 'GMT-8'; SET VARIABLE play=> select '2000-01-05 00:00:59.00+08'::timestamp + '0.999 sec'::interval; ?column? ---------------------------2000-01-05 00:00:60.00+08 (1 row) That's clearly a bug. Evidently the rounding to 2 fractional digits needs to be done before we start conversion, not at the end, since in the worst case the effects could need to propagate all the way to the displayed year. However, that shouldn't matter for now() results, because AFAIK now() results should always be exact integer seconds. Are you sure you weren't doing arithmetic on these values somewhere along the line? In any case, I'm pretty sure nothing's changed in the timestamp code between 7.0.2 and 7.0.3, and I know of no changes that could explain your original report either. I'm not sure why your update went so disastrously --- I'm wondering about possibilities like a corrupted download of Postgres. What platform are you on (I think you said Linux, but which release of which distro)? Where and how did you obtain your Postgres files; if you compiled them yourself, how did you configure and compile? > migrate=# select version(); > version > --------------------------------------------------------------- > PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 That's a rather interesting version report, seeing as how there is no such gcc release as 2.95.3 according to the GCC project's homepage. What compiler are you using exactly, and what confidence do you have that it's bug-free? You wouldn't be using that known-unstable gcc that RedHat shipped in their 7.0, would you? regards, tom lane