Обсуждение: Problem with restore on upgrading to 7.1

Поиск
Список
Период
Сортировка

Problem with restore on upgrading to 7.1

От
Joseph Shraibman
Дата:
I just updated to 7.1 and tried to restore my database that was dumped
with 7.0.3

psql:dumpall-2001-4-27:8452: ERROR:  copy: line 8933, Bad timestamp
external representation '2001-01-17 19:37:60.00-05'
psql:dumpall-2001-4-27:8452: PQendcopy: resetting connection
CREATE
CREATE
<snip>

[root@d1 /var/lib/pgsql] tail +8933  dumpall-2001-4-27 | head
372     2       76124   2001-01-17 20:38:47.54-05       2001-01-17
20:38:50-05 12       13

[root@d1 /var/lib/pgsql] grep "19:37:60" dumpall-2001-4-27
175     2       71904   2001-01-17 19:37:60.00-05       2001-01-17
19:38:01-05 12       13

It appears that all my data was restored except for the table which had
the error(which happened to be my biggest table) of which none was
restored.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Re: Problem with restore on upgrading to 7.1

От
Tom Lane
Дата:
Joseph Shraibman <jks@selectacast.net> writes:
> psql:dumpall-2001-4-27:8452: ERROR:  copy: line 8933, Bad timestamp
> external representation '2001-01-17 19:37:60.00-05'

Are you on Mandrake by any chance?  Looks like your 7.0 installation
had that infamous roundoff problem.

I recommend manually changing "37:60" to "38:00" in the dump file,
then you should be able to load it.

            regards, tom lane

Re: Problem with restore on upgrading to 7.1

От
Joseph Shraibman
Дата:
No, I have a redhat 6.x system and I built the postgres myself from the
7.0.3 source.

Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> > psql:dumpall-2001-4-27:8452: ERROR:  copy: line 8933, Bad timestamp
> > external representation '2001-01-17 19:37:60.00-05'
>
> Are you on Mandrake by any chance?  Looks like your 7.0 installation
> had that infamous roundoff problem.
>
> I recommend manually changing "37:60" to "38:00" in the dump file,
> then you should be able to load it.
>
>                         regards, tom lane

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Timestamp bug WAS: Problem with restore on upgrading to 7.1

От
Joseph Shraibman
Дата:
owl=# select '2001-05-01 20:57:59.999-04'::timestamp;
         ?column?
---------------------------
 2001-05-01 20:57:60.00-04
(1 row)

owl=# select version();
                              version
-------------------------------------------------------------------
 PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)


So it appears this is a bug in 7.1 as well.  The problem is in
postgresql-7.1/src/backend/utils/adt/datetime.c line 2099:
sprintf((str + 17), ((fsec != 0) ? "%05.2f" : "%02.0f"), sec);

..which just tells sprintf to round the seconds.  Line 2137 should also
be fixed.

When this is fixed the fix should be backported to 7.0.x so people can
dump for upgrading to 7.1.x.

Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> > psql:dumpall-2001-4-27:8452: ERROR:  copy: line 8933, Bad timestamp
> > external representation '2001-01-17 19:37:60.00-05'
>
> Are you on Mandrake by any chance?  Looks like your 7.0 installation
> had that infamous roundoff problem.
>
> I recommend manually changing "37:60" to "38:00" in the dump file,
> then you should be able to load it.
>
>                         regards, tom lane

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com