Problem with restore on upgrading to 7.1
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
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
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
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