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