Обсуждение: 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