Thread: Unable to format timestamp with time zone; internal coding error
Hi all,
We have suddenly encountered a problem with a v7.2.1 database
pg_dumpall. The output looks as follows:
connected to template1...
dumping database "cws"...
pg_dump: ERROR: Unable to format timestamp with time zone; internal
coding error
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "alr_bpc_list"
failed: PQendcopy() failed.
pg_dump: Error message from server: pg_dump: The command was: COPY
"alr_bpc_list" TO stdout;
pg_dump failed on cws, exiting
Searching the lists shows mention of this error in early feb as a bug.
One option for us is to upgrade to v7.3 - but to do this we need to dump
the database, and this is failing.
Does anyone have a patch for this bug we can apply to v7.2.1?
Can anyone explain further what this bug means?
Regards,
Graham
--
-----------------------------------------
minfrin@sharp.fm "There's a moon
over Bourbon Street
tonight..."
Graham Leggett <minfrin@sharp.fm> writes:
> pg_dump: ERROR: Unable to format timestamp with time zone; internal
> coding error
AFAICT this error can only appear when trying to display a timestamp
that's before the start of the Julian day count, 4713 BC ... which the
system should never have let you enter in the first place. (In my
tests, the timestamp input routine rejects such dates.) Got any idea
how you got that value in there?
regards, tom lane
Tom Lane wrote:
>> pg_dump: ERROR: Unable to format timestamp with time zone; internal
>>coding error
>
>
> AFAICT this error can only appear when trying to display a timestamp
> that's before the start of the Julian day count, 4713 BC ... which the
> system should never have let you enter in the first place. (In my
> tests, the timestamp input routine rejects such dates.) Got any idea
> how you got that value in there?
Absolutely no idea. Could vacuuming the database affect it?
Any idea what solution I can use to fix this? We can no longer back up
our database any more, which is a critical problem.
Why is this a fatal error? Is there a way of turning this into a warning
so we can at least get the rest of the data out?
Regards,
Graham
--
-----------------------------------------
minfrin@sharp.fm "There's a moon
over Bourbon Street
tonight..."
Tom Lane wrote:
>> pg_dump: ERROR: Unable to format timestamp with time zone; internal
>>coding error
I notice that v7.2.3 was released, but noone made RPMs for it, and the
spec file in the release is hosed:
[root@trilobite ~]# rpm -tb postgresql-7.2.3.tar.gz
error: File /root/PyGreSQL-3.0-pre20000310.tgz: No such file or directory
Are there any RPMs available anywhere for 7.2.3 that might fix this problem?
Regards,
Graham
--
-----------------------------------------
minfrin@sharp.fm "There's a moon
over Bourbon Street
tonight..."
Would it be a good idea to shutdown postgresql and then make a backup copy of the entire /data/ directory. Then restart postgresql and try to see how many rows have allegedly erroneous timezone? e.g. do a select count(*) where date < some really old date here, to see how many rows affected. Then do a select, and then update those bad dates if only a few rows affected. If that works then you might be able to dump your database and upgrade. Not sure if it would be helpful to dump stuff table by table using pg_dump - might be useful to check which table has dump problems. Good luck, Link. At 04:26 PM 12/22/02 +0200, Graham Leggett wrote: >Tom Lane wrote: > >>> pg_dump: ERROR: Unable to format timestamp with time zone; internal >>> coding error > >I notice that v7.2.3 was released, but noone made RPMs for it, and the >spec file in the release is hosed: > >[root@trilobite ~]# rpm -tb postgresql-7.2.3.tar.gz >error: File /root/PyGreSQL-3.0-pre20000310.tgz: No such file or directory > >Are there any RPMs available anywhere for 7.2.3 that might fix this problem? > >Regards, >Graham >-- >----------------------------------------- >minfrin@sharp.fm "There's a moon > over Bourbon Street > tonight..." > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html >
Graham Leggett <minfrin@sharp.fm> writes:
> Any idea what solution I can use to fix this?
Find the row (or rows) containing the bad value, and delete or update
it/them. It's possible that this is just one symptom of a data
corruption problem, so you probably should take a close look at the
other fields of the bad rows while you are at it.
regards, tom lane