r irussel wrote:
> Hello Every body:
>
> I have implemented psql version 7.4.2 on Debian linux version 3.2
> and it was running very well. At this moment it is being crashed when
> executing a single select statement. Details are mentionded as
> follows:
You really should be running something later than 7.4.2 - there are a
lot of bug-fixes between that and 7.4.12
> DB Dump size before this problem is arised: 95 MB DB ENCODING is
> "EUC_JP"
>
> After some investigation the problem is narrowed down :
>
> -- A table namly t60 has 411120 rows .
> --There is no Index for the t60 table .
> -- t60 has column named c1, c2, c3 .
> -- Statement " SELECT c1, c3 FROM t60 " Returns All rows of specified
> coumn.
>
> But when execute Statement " SELECT c2 FROM t60 " causes Error like
>
> 1." Invalid memory alloc request size 82127290 " 2. "ERROR: out of
> memory DETAIL: Failed on request of size 875573295"
It looks like column c2 on t60 has been corrupted on at least one row.
Have you experienced any crashes on this database?
Anyway - the simplest thing to do is to copy the good rows into another
table and skip only the damaged data.
1. CREATE TABLE new_t60 AS SELECT * FROM t60 LIMIT 0;
2. INSERT INTO new_t60 SELECT * FROM t60 WHERE c1 BETWEEN ??? AND ???
Adjusting the ??? will let you work around the problem row(s).
Column c1 might not be the best choice - your primary key is what you
want to use.
3. Dump the new table's data.
4. Drop table t60 (remove any foreign-key references and turn off
triggers first)
5. Recreate table t60
6. Restore new_t60's data into t60 and restore foreign-key references etc.
Then, you can dump the new table and restore it with just the damaged
row(s) needing to be replaced/repaired.
Then, you need to upgrade to 7.4.12 and also figure out why this
happened. Have you had crashes? Is your RAM good? Are your disks syncing
when they say they are?
--
Richard Huxton
Archonet Ltd